data positioning with respect to date and name

[nab92002]

Dear All,

I am trying to bring data in a form that requires to copy and paste data to the corresponding years and name. The original data file has some years missing which i need to mention blank in Required Data set. As well as some data values are not required specifically named with Divn. Please have a look at the attached sheet and help me out to resolve this issue. The first sheet named "Data" has the original data and required data sheet is the way i want it to be. i have done for the first cohort manually.

[AliGW]

So all we are doing is copying the data with certain columns removed - is this it?

If so, load the source table into PowerQuery, remove the unnecessary columns and close and load to a different worksheet.

[nab92002]

No we are adding columns for some missing years for each cohort and in total they are 63 of them and addition of minimum 8 coulms or some cohorts and for others its 25 columns or years.

[AliGW]

Columns? Do you mean rows? Just add the missing years to the bottom and then sort the data by the year column.

[nab92002]

yes rows really sorry

[nab92002]

I have attached a sample file. please have a look at the required data sheet as i have to do this for all 63 cohorts adding years manually and then sorting it requires alot of time. Using a Formula or method by which i can link values in sheet named "data" to sheet named required data w . r . t corresponding name and year will reslove my issue.
I know little about excel.

[AliGW]

Try this:

=IFNA(LOOKUP(2,1/((Data!$A$2:$A$2315=A2)*(Data!$B$2:$B$2315=B2)*(Data!$C$2:$C$2315=C2)*(Data!$D$2:$D$2315=D2)),Data!E$2:E$2315),"")

or:

=IFERROR(1/(1/LOOKUP(2,1/((Data!$A$2:$A$2315=A2)*(Data!$B$2:$B$2315=B2)*(Data!$C$2:$C$2315=C2)*(Data!$D$2:$D$2315=D2)),Data!E$2:E$2315)),"")

[nab92002]

oh thanks, i will try and update about it.

[nab92002]

I have tried both but there is a problem these formulas are only giving values fro intital years but not for the latter years, it would be kind of yours if you can apply them and see if i am wrong or oherwise.

[AliGW]

Sorry - try this:

=IFNA(LOOKUP(2,1/((Data!$A$2:$A$2315=$A2)*(Data!$B$2:$B$2315=$B2)*(Data!$C$2:$C$2315=$C2)*(Data!$D$2:$D$2315=$D2)),Data!E$2:E$2315),"")

or this:

=IFERROR(1/1/LOOKUP(2,1/((Data!$A$2:$A$2315=$A2)*(Data!$B$2:$B$2315=$B2)*(Data!$C$2:$C$2315=$C2)*(Data!$D$2:$D$2315=$D2)),Data!E$2:E$2315)),"")

[nab92002]

Dear AliGW it is working only for the first 4 after that its not working. Please have a look

[AliGW]

That's because the numbers in column B do not match, so the formula returns blank.

Maybe try this:

=IFNA(LOOKUP(2,1/((Data!$A$2:$A$2315=$A2)*(Data!$C$2:$C$2315=$C2)*(Data!$D$2:$D$2315=$D2)),Data!E$2:E$2315),"")

[nab92002]

Thanks AliGW it is working. I am really thankfull to you.

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.