Complicated sort

I

intheway

I was wondering if anyoen could assit me: Columnns A-F in each ro
represent data for an individual. Then rowns H-M represent data for
different individual. Here is the tricky part - both sets of data (A-
& H-M) sometimes contain the same individual while there are peopl
that exist in H-M and not in A-F. If you are still with me - how can
tell excel to place matching person side by side 9A-F & H-M) and leav
a blank set of cells where they do not match. In other words, if th
same person exists A-F then then H-M should also contain the sam
information. The persons's first name is in columns B&I and their las
name are in columns D&K.

Thank you :cool
 
F

Frank Kabel

Hi
a complicated procedure but you may give it a try:
0. Sort only the range A1:Fx

1. Enter the following formula as array formula (with CTRL+SHIFT+ENTER)
in N1:
=MATCH(1,(I1=$B$1:$B$100)*(K1=$D$1:$D$100),0)
and copy this formula down for ALL filled rows for column I

After this you should have either the row number of the matching entry
in column a or the error value #NA.

2. Lets say you have copied this formula down to cell N50 then enter
the following array formula in N51:
=MATCH(0,COUNTIF($N$1:N50,ROW(INDIRECT("1:"&COUNTA($B$1:$B$100)))),0)
and copy this formula down unless you get the #NA error again

3. Now select your range H1:Nx and sort with column N as first criteria
and the last name column (K) as second criteria

After this the names should be next to each other.
 
Top