Suggestions on how to compile two sheets into one using unique values

[spectraflame]

I need to combine data from two sheets into one based on employee number. Sheet 1 has all of the data in a single row for each employee. Sheet 2 has all of the employees dependent related information on multiple rows. Both sheets have a unique number for each employee. The number of related employee records from Sheet 2 varies based on the number of dependents. There are 4 specific fields for each record from Sheet 2 that need to be combined with the rows in Sheet 1. Does anyone have suggestions on a way to combine this other than manually copying the data from one to the other?

 

[etaf]

you could use filter , maybe with a vstack
but i'm not 100% sure how you want to have the combined data

sheet1 is 1 row per ID
sheet2 is multiple rows per ID

if its only 4 fields then a lookup would do

assuming your unique id is in say column A and you wanted the results in column W,X,Y & Z on sheet1 the 4 fields from sheet2
and on sheet2 the ID is also in column A say , and in sheet2 the columns with the 4 lots of data are in E,F,G & H

In Sheet1 column W W2
=index(sheet2!E$2:E$10000, match($A2, sheet2!$A$2:$A$1000,0))
and copy across and down

so change
sheet2!E$2:E$10000,
to however many rows you need , doesnt matter if its a lot more ,as long as you cover all the possible rows
and then change

E to whatever column the data you want is in

 

[kvsrinivasamurthy]

Sheet1 headings range is A2:Y2. Data A3:Y25. Unique number is in Column G.
Sheet2 headings range is A2:AR2. Data A3:AR20..Unique number is in Column J.
In Sheet2
In AS2

=IFERROR(INDEX(Sheet1!$A$2:$Y$2,SMALL(IF(COUNTIF($A$2:$AR$2,Sheet1!$A$2:$Y$2)=0,COLUMN(Sheet1!$A$2:$Y$2),""),COLUMNS($A1:A1))),"")

Drag across till blank result is seen.
In AS3

=IFERROR(INDEX(Sheet1!$A$3:$Y$25,MATCH($J3:$J20,Sheet1!$G$3:$G$25,0),MATCH(AS$2,Sheet1!$A$2:$Y$2,0)),"")

Drag across .