Find a Match in Multiple Places & Return Multiple Values

  • Thread starter Thread starter Toria
  • Start date Start date
T

Toria

Hello,

I've spent hours on this and still can't get the formula right. I have a
list of phone numbers in column A on worksheet 1 and another list phone
numbers in column A on worksheet 2. I need a formula on worksheet 1 that
says if the phone number matches on worksheet 2, then place the contents from
column D on worksheet 2 into worksheet 1. The phone number will match more
than once on worksheet 2, however, so I need will need Excel to be able to
return multiple values (which is all text). Thank you in advance for any
help you can provide!
 
Here's one way to deliver it dynamically w/o too much fuss ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3igc5
Extracting dups from sht2 horizontally for uniques in sht1.xls

Data assumed in row 2 down in both Sheet1/2
where Sheet1, col A contains the unique phone numbers &
Sheet2, col A contains the duplicate phone numbers (in any order)

In Sheet2,
In F2:
=IF($A2="","",IF($A2=OFFSET(Sheet1!$A$1,COLUMNS($A:A),),ROW(),""))
Copy F2 across by as many cols as there are unique phone numbers
listed/expected in Sheet1, then fill down to cover the max expected extent of
data in col A. Leave the top row (ie F1 across blank)

Then in Sheet1,
In B2:
=IF(COLUMNS($A:A)>COUNT(OFFSET(Sheet2!$E:$E,,ROWS($1:1))),"",INDEX(Sheet2!$D:$D,SMALL(OFFSET(Sheet2!$E:$E,,ROWS($1:1)),COLUMNS($A:A))))
Copy B2 across by the maximum expected number of duplicates per any unique
phone number (in Sheet2), say across by 6 cols? to G2, fill down. This will
return the required results, ie the duplicate text from Sheet2's col D into
cols B to G for each of the unique phone numbers listed in A2 down, with all
results neatly bunched to the left.
 
Thanks, Max! I really appreciate it!!

Max said:
Here's one way to deliver it dynamically w/o too much fuss ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3igc5
Extracting dups from sht2 horizontally for uniques in sht1.xls

Data assumed in row 2 down in both Sheet1/2
where Sheet1, col A contains the unique phone numbers &
Sheet2, col A contains the duplicate phone numbers (in any order)

In Sheet2,
In F2:
=IF($A2="","",IF($A2=OFFSET(Sheet1!$A$1,COLUMNS($A:A),),ROW(),""))
Copy F2 across by as many cols as there are unique phone numbers
listed/expected in Sheet1, then fill down to cover the max expected extent of
data in col A. Leave the top row (ie F1 across blank)

Then in Sheet1,
In B2:
=IF(COLUMNS($A:A)>COUNT(OFFSET(Sheet2!$E:$E,,ROWS($1:1))),"",INDEX(Sheet2!$D:$D,SMALL(OFFSET(Sheet2!$E:$E,,ROWS($1:1)),COLUMNS($A:A))))
Copy B2 across by the maximum expected number of duplicates per any unique
phone number (in Sheet2), say across by 6 cols? to G2, fill down. This will
return the required results, ie the duplicate text from Sheet2's col D into
cols B to G for each of the unique phone numbers listed in A2 down, with all
results neatly bunched to the left.
Click to expand...
 
Back
Top