Arranging values from Sheet2 to Sheet1

[karthick87]

I have an excel file with 2 Sheets containing the below details.

Sheet2 has the actual data and Sheet1 does have any entry's apart from the header. I wanted to refer Sheet2 and note down the ID and respective answers in the format given in Sheet1. Is it achievable using some formula?

Sheet2: Is the actual data
Sheet1: Expected Output (How the results should be)

I have attached a sample excel sheet with this message for your reference.

 

[David Evans]

Here it is using Power Query - or Get and Transform Data, depending on your Excel version.

Let us know if you've not used it and we can walk you through it - this one is very straightforward.


BTW, if you're importing the table from another data source, you can probably forgo that exercise and simply link directly to it through Power Query or create a Data Connection. Invariably, data that needs to be "unpivoted" has been pivoted outside excel prior to arriving

 

[bosco_yip]

A formula way,

In B2, formula copied across right to F2 and all copied down :

=IFERROR(INDEX(Sheet1!$B$1:$B$13,AGGREGATE(15,6,ROW(Sheet1!$B$1:$B$13)/(Sheet1!$A$1:$A$13=$A2),COLUMNS($A:A))),"")

Regards
Bosco

 

[karthick87]

@bosco_yip thank you for helping me here. Just wanted to know, how you have copied the formula in B2? Because applying the values in B2 replaces the cell value.

 

[bosco_yip]

@bosco_yip thank you for helping me here. Just wanted to know, how you have copied the formula in B2? Because applying the values in B2 replaces the cell value.

Remove the existing value in B2, enter the formula, copied across and down as per post #.3 mentioned and attached file.

Regards

 

[karthick87]

@bosco_yip I believe I haven't explained the issue in detail. I have attached a Sample_Data.xlsx with this message for more understanding. In Sheet "Applied Formula", I have applied the formula in B2 and dragged the formula up to G2. But after applying the formula, the cell value (i.e B2 value 'Leopard' is not visible and only the formula is visible). Also in Sheet1, the data's are added.

Sheet "Actual" has the original data (without applying the formula). I am not sure, where I am making the mistake. Sorry for troubling you and thanks for all the help.

Screenshot: After applying the formula, my data in B2 disappeared.

 

[bosco_yip]

@bosco_yip I believe I haven't explained the issue in detail. I have attached a Sample_Data.xlsx with this message for more understanding. In Sheet "Applied Formula", I have applied the formula in B2 and dragged the formula up to G2. But after applying the formula, the cell value (i.e B2 value 'Leopard' is not visible and only the formula is visible). Also in Sheet1, the data's are added.

Sheet "Actual" has the original data (without applying the formula). I am not sure, where I am making the mistake. Sorry for troubling you and thanks for all the help.

Screenshot: After applying the formula, my data in B2 disappeared.
View attachment 45517

Please checking your attached file Sheet1 !

the source table in Column A & B doesn't content data (or, all cells giving blank) ?

that causing B2 formula returned in blank.

Regards
Bosco

 

[karthick87]

@bosco_yip Yep now I understood the issue.

Actually, the requirement is, only in Sheet2 I will be having the actual data. In sheet1 I will be only having the header (i.e id (A1) animals(B1)). So comparing the Sheet2, data's has to be filled in Sheet1 automatically.

To give you more clarity on my requirement.

Sheet2: (Containing the Actual Data - Fixed Data)



Sheet1: (Will be only having the headers (i.e id animals))



Expected Output in Sheet1: (By Applying some formula in Sheet1 (i.e A2 & B2), I need to get the below results automatically)

 

[bosco_yip]

@bosco_yip Yep now I understood the issue.

Actually, the requirement is, only in Sheet2 I will be having the actual data. In sheet1 I will be only having the header (i.e id (A1) animals(B1)). So comparing the Sheet2, data's has to be filled in Sheet1 automatically.

To give you more clarity on my requirement.

Sheet2: (Containing the Actual Data - Fixed Data)

View attachment 45525

Sheet1: (Will be only having the headers (i.e id animals))
View attachment 45526


Expected Output in Sheet1: (By Applying some formula in Sheet1 (i.e A2 & B2), I need to get the below results automatically)

View attachment 45527

See attached revised file

Regards
Bosco

 

[karthick87]

@bosco_yip so it worked perfectly apart from a minor in the result.

Only 5 responses (A2:A6) has copied down for id(1). Please see the below results



However, in our data, we have 6 responses (B2:G2). Please see the data below.



So the last response for id1 (G2) is not getting copied to Sheet1.

 

[bosco_yip]

@bosco_yip so it worked perfectly apart from a minor in the result.

Only 5 responses (A2:A6) has copied down for id(1). Please see the below results

View attachment 45529

However, in our data, we have 6 responses (B2:G2). Please see the data below.

View attachment 45530

So the last response for id1 (G2) is not getting copied to Sheet1.

Here 's the revised formula and the attached file,

Regards
Bosco