merge data from array without removing column duplicates

**aversluis** · 09-26-2007, 12:20 PM

Hello,

I am looking for help (worksheet function or VBA code) that will automate the following.

I have a data array in which the first column contains duplicates. For example these 3 columns with 4 entries, 2 of which are entries for Susan who has both a work and home number:

NAME NUMBER TYPE
Susan 3456 home
Andre 4321 work
Susan 6789 work
Keith 5432 cell

I want to transform/merge the data as follows, in this case to 4 columns with three entries:

NAME HOME WORK CELL
Susan 3456 6789 -----
Andre 4321 ----- -----
Keith ----- ----- 5432

Any help is appreciated. Thanks.

**VBA Noob** · 09-26-2007, 12:33 PM

Maybe a pivot.

See attachment and link

http://www.datapigtechnologies.com/f...es/pivot1.html

VBA Noob

**Mark@Work** · 09-26-2007, 12:38 PM

Originally Posted by aversluis

NAME NUMBER TYPE
Susan 3456 home
Andre 4321 work
Susan 6789 work
Keith 5432 cell

I want to transform/merge the data as follows, in this case to 4 columns with three entries:

NAME home work cell
Susan 3456 6789 -----
Andre 4321 ----- -----
Keith ----- ----- 5432

Assuming this is in Cells A1:C5 of Sheet1 and your final list is in Cells A1:D4 of Sheet2;
Set cell B2 of Sheet 2 to:
{=INDEX(Sheet1!$B$2:$B$5,MATCH($A2&B$1,Sheet1!$A$2:$A$5&Sheet1!$C$2:$C$5,0))}
Note that this is an ARRAY formaula so use CTRL SHIFT ENTER rather than just ENTER.

ALSO note that you MUST ensure that the entries are all spelt exactly the same way in both sheets with the same capitalisation etc.

Mark.