Excel possibly MATCH/CHOOSE problem

  • Thread starter Thread starter andyp161
  • Start date Start date
A

andyp161

I am trying to merge two worksheets together based on identificatio
numbers included in column A of both worksheets. The first workshee
contains data on 2000 people (A1:N2001), the second contains extra dat
on 1824 (A1:G1825) of those 2000 people. Therefore, what I want to d
is place the extra data on those 1824 people to the right of thei
corresponding data in the first worsheet by way of the identificatio
numbers of each person contained in column A of both worksheets.

Kind regard
 
I assume that row 1 contain titles
Try the following

in Sheet1!O2 use the following formula

=INDEX(Sheet2!$B$1:$G$1825,MATCH(INDIRECT($A2,Sheet2!
$A$1:$A$1825,0),1)

MATCH(INDIRECT(A2,Sheet2!$A$1:$A$1825,0) returns the row
number of sheet2 with the same person number than the one
in A2

INDEX(Sheet2!$B$1:$G$1825,MATCH(...),1) returns the
contents of the cell of range Sheet2!B1:G1825 on the
matching row and column B

Now move formula to cell P2 and replace the last 1 by 2
in order to get data from column C of sheet2.
And so on until column T (that will receive data from
column G)

Then you can drag formula in O2:T2 until 02001:T2001
and you will rapatriate data from sheet 2 to sheet 1

However not all persons are referenced in sheet 2 and you
will have some errors. To avoid that, the formula should
be changed into

=if ISNA(INDEX(...),"",INDEX(...)

where INDEX(...) is the complete formula above

so if there is no matching entry the cell is left blank


An alternate solution is to use in all the cells you need
to fill the formula :


=If(ISNA(INDEX(Sheet2!$B$1:$G$1825,MATCH(INDIRECT("R"&ROW()
&"C1",FALSE),Sheet2!$A$1:$A$1825,0),COLUMN()-14)),"",INDEX
(Sheet2!$B$1:$G$1825,MATCH(INDIRECT("R"&ROW()
&"C1",FALSE),Sheet2!$A$1:$A$1825,0),COLUMN()-14))

Same principle but more automation

Column()-14 calculates the column number where to pick
data from table B1:G1825. in column O the formula gives 1,
in column P, 2 etc...


INDIRECT("R"&ROW()&"C1",FALSE) calculates the address of
the cell in column A of the row of the cell containing the
formula.

Regards
Jacques
 
One way to try ..

Assuming:
the first worksheet is "Sheet1",
second worksheet is "Sheet2",
data starts from row2 down
in both Sheet1 and Sheet2

In Sheet1
-------------
Put in O2:

=IF(ISNA(MATCH($A2,Sheet2!$A:$A,0)),"",OFFSET(Sheet2!$A$1,MATCH($A2,Sheet2!$
A:$A,0)-1,COLUMN(A1)))

Copy O2 across 6 cols to T2, then copy down to T2001
 
Back
Top