worksheet function – Excel: Help Formatting Columns from Another Sheet

I am trying to reformat an existing excel sheet in a new sheet.

The workbook has 2 sheets. Sheet 1 is called Responses and sheet 2 is called Question.

The format of the Responses sheet is:

|---------------------|---------------------|------------------|------------------|
|          ID         |      Question1      |     Question2    |    Question3     |
|---------------------|---------------------|------------------|------------------|
|          1          |          5          |         5        |         5        |
|---------------------|---------------------|------------------|------------------|
|          2          |          4          |         4        |         4        |
|---------------------|---------------------|------------------|------------------|
|          3          |          3          |         3        |         3        |
|---------------------|---------------------|------------------|------------------|
|          4          |          2          |         2        |         2        |
|---------------------|---------------------|------------------|------------------|
|          5          |          1          |         1        |         1        |
|---------------------|---------------------|------------------|------------------|

The format of the Question sheet is:

|---------------------|---------------------|------------------|
|          ID         |      Question       |       Value      |
|---------------------|---------------------|------------------|
|          1          |      Question1      |         5        |
|---------------------|---------------------|------------------|
|          2          |      Question1      |         4        |
|---------------------|---------------------|------------------|
|          3          |      Question1      |         3        |
|---------------------|---------------------|------------------|
|          4          |      Question1      |         2        |
|---------------------|---------------------|------------------|
|          5          |      Question1      |         1        |
|---------------------|---------------------|------------------|
|          1          |      Question2      |         5        |
|---------------------|---------------------|------------------|
|          2          |      Question2      |         4        |
|---------------------|---------------------|------------------|
|          3          |      Question2      |         3        |
|---------------------|---------------------|------------------|
|          4          |      Question2      |         2        |
|---------------------|---------------------|------------------|
|          5          |      Question2      |         1        |
|---------------------|---------------------|------------------|
|          1          |      Question3      |         5        |
|---------------------|---------------------|------------------|
|          2          |      Question3      |         4        |
|---------------------|---------------------|------------------|
|          3          |      Question3      |         3        |
|---------------------|---------------------|------------------|
|          4          |      Question3      |         2        |
|---------------------|---------------------|------------------|
|          5          |      Question3      |         1        |
|---------------------|---------------------|------------------|

Formula to populate the ID from the Responses Sheet to the Question Sheet however it increments while I need it to follow the pattern in the Question table:

=IF(INDIRECT("Responses!A:A")=0,"",INDIRECT("Responses!A:A"))

So far I can only get the first 5 responses of each column and I am not sure how to get the header value that corresponds to it in the Question column.

Here is the formula that I have so far to populate the Value column from the Responses sheet however it is limited to the values in 1 column.

=IF(INDIRECT("Responses!B2:B40000")=0,"",INDIRECT("Responses!B2:B40000"))

Thank you!

magento2 – 2 columns left layout not working anymore on CMS pages?

Magento 2.3.3

I had the 2 columns left layout working until I changed it to 1 column and then back to 2 columns left. Now the view changes after switching, but it is not 2 columns left. So I tried setting it to 2 columns right and it looks exactly the same as left.

I checked the code and this is whats it looks for 2 columns left or right: https://i.imgur.com/tFYNeeG.png. I cleared the cache, recompiled redeployed, nothing made it work again. I also tried adding a new CMS page, same problem. Maybe the template files for the layouts are missing? Where would I find those on the server?

Any ideas?

group by in query google sheet select different columns

Want to get the results described on the screenshot. How can i achieve it. Thank you.

enter image description here

QUERY:

=QUERY(A2:F12,"SELECT A, MAX(B), MIN(B) group by A label A 'G V1', MAX(B) 'LAST V2', MIN(B) 'FIRST V2'")

Want to get First & last V5 value in terms of max and min dates in V2 (see K2, L2 desired results), I could get the last and first dates now i want their V5 corresponding.

How to Pivot Multiple Columns in SQL Server

welcome to dba.stackexchange. It might help to paste your (unsuccesful) queries and the errors you got.

I am sorry other tipps and tutorials did not help you with your challenge. Since you are specifically asking for pivot, I suggest another documentation that appears (to me) to be quite straight forward:
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

The basic pivot form looks like this:

SELECT <non-pivoted column>,  
  (first pivoted column) AS <column name>,  
  (second pivoted column) AS <column name>,  
  ...  
  (last pivoted column) AS <column name>  
FROM  
  (<SELECT query that produces the data>)   
  AS <alias for the source query>  
PIVOT  
(  
   <aggregation function>(<column being aggregated>)  
FOR   
   (<column that contains the values that will become column headers>)   
   IN ( (first pivoted column), (second pivoted column),  
   ... (last pivoted column))  
   ) AS <alias for the pivot table>  
<optional ORDER BY clause>;  

In your case that might turn out to something along those lines (I did not test this and have not finished it all):

SELECT TimesOfDay,  
  (1) AS Monday,  
  (2) AS Tuesday,  
  ...  
  (last pivoted column) AS <column name>  
FROM  
  (<SELECT query that produces the data>)   
  AS <alias for the source query>  
PIVOT  
(  
   sum(item_count)  
FOR   
   (day_of_week)   
   IN ( (1), (2),  
   ... (7))  
   ) AS <alias for the pivot table>  
<optional ORDER BY clause>;  

Hope that helps
Andreas

Convert columns of ISO 8601 strings to dates in Google Sheets

I’m pulling data to my sheet from an API using an ImportJSON function. The data array contains mixed data, including several columns of ISO 8601 date strings in UTC. For example:

2020-06-02T00:00:00Z
2020-06-02T03:00:00Z
2020-06-02T14:30:00Z

Google Sheets doesn’t apparently recognize these as dates, or at least applying a date format to these columns doesn’t do anything. Can I wrap a formula around the ImportJSON function that would parse certain columns to format the date and time properly?

php – How to insert multiple rows and columns in database using array

Please i want to know if it is posible to use one sql query to insert into multiple columns and rows using array in php
$sql = "INSERT INTO MyGuests (firstname, lastname, email) <br> VALUES ('John', 'Doe', 'john@example.com');";
but i want to represent column names in array as arrays and then values as array values, something that will look like this in php
$sql = "INSERT INTO myguest (array(firstname => 'john'));

Shifting and adding the columns of two lists

Let’s say that I have this list

a={{-2, 1}, {-1, 2}, {0, 1}, {1, 0}, {2, 0}}

and I’d like to change the value of the elements in the column to obtain something like this

b={{-2, 0}, {-1, 0}, {0, 1}, {1, 2}, {2, 1}}

so, you can see that it’s like I’m shifting the values in the column two positions.

and then I’d like to sum the elements in the column to obtain

c={{-2, 1}, {-1, 2}, {0, 2}, {1, 2}, {2, 1}}

Please, help! Is it possible to fill the 10×10 square by the numbers -1, 0, 1 so the 20 sums of the numbers in the rows and columns are all different?

Is it possible to fill the 10×10 square by the numbers -1, 0, 1 so the 20 sums of the numbers in the rows and columns are all different?

javascript – Make the columns of the SharePoint Online list read-only according to the yes / no check box

I have 7 columns that I need to do read-only (grayed out) as soon as the item is saved when "Field 7" is checked (yes / no checkbox column).

I managed to make these 7 columns read-only using the following Javascript, but I am fighting with the IF statement. Any help would be appreciated.

    


php – Custom sorting in publication columns by ACF Pro Select Field

I am adding a custom column to the admin screen for a custom post type. The custom column should display the options selected in an ACF Pro selection field. Instead, it always fires as containing the value, which suggests to me that instead of pulling only the options selected for each post, it pulls all of the available values ​​for the ACF Pro selection field.

I'm just … not sure how to fix this. What am I wrong here?

add_action( 'manage_asgallery_posts_custom_column', 'asgallery_new_column', 10, 2);
function asgallery_new_column( $column_name, $post_id ) {
    if( $column_name == 'featured_posts' ) {
        $a = get_field_object('field_5dbcb72cad947', $post_id); 
        $a_value = $a('value');
        //$case_study = get_post_meta( $post_id, 'case_study_category' );
        if(strpos($a_value, 'Repair') !== false) {
            echo 'Repair'; 
        }
        else { echo 'None'; }
    }
}