0

I have a contact list from hell that needs cleaning up. The basic problem is this: the company name is merged vertically and there multiple rows of information for each company (with blank cells everywhere).

Screenshot of Excel Issue

enter image description here

I have created a second sheet and was successful in using a formula I found on stack overflow for getting the data from the 2nd column (where the data was on the top of 3 rows): 

=INDEX('Sheet1'!D:D,MATCH(A4,'Sheet1'!A:A,0))

However, the same formula doesn't work when the needed data is on the second row.

I have thought about trying to unmerge column A, then duplicating the data from the merged cell to each unmerged cell. But I'm afraid that having 3 matching cells will return blanks with the formula above.

*I am not a programmer in the least, but I've found stack overflow very helpful for working with Excel. Thanks for your patience with me and I very much appreciate any help you could give.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Jahana Uchtman
  • 1
  • Are the always one row down? If so then simply `=INDEX('Sheet1'!D:D,MATCH(A4,'Sheet1'!A:A,0)+1)` will pull the value from column D, 1 row down from where the match is found. – Scott Craner Apr 29 '16 at 19:41
  • Thanks @ScottCraner. I suppose I didn't give a great example- The email address is not always in the second row of the three. I aprreciate the idea though! – Jahana Uchtman May 01 '16 at 02:39

1 Answers1

0

A single sample is not really enough to explain the issue, but the following might help, where the single example is highlighted:

SO36946247 example

ColumnA is after unmerging. The formula in D1 (copied across to E1 and D1:E1 then copied down to suit) is:

=TRIM(IF($A1<>"",B1&" "&B2&" "&B3,""))
pnuts
  • 58,317
  • 11
  • 87
  • 139