Unable to Search Merged Cells with V-lookup in Excel 2007

I receive quarterly database update files up to 20,000 rows long. Column A contains some merged cells. I need to perform v-lookups to compare my data to their file information in column B & C. This gives me all the applicable rev codes for any given procedure code.

1) How do I perform a v-lookup with multiple results possible?

2) Is there a way to un-merge the cells in column A and duplicate the correct procedure code into the newly created blank cells?

I greatly appreciate any assistance, and there is no rush.
 Example-1.xlsx
nts42aAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
roger_karamConnect With a Mentor Commented:
Both vlookup and pivot tables use the first result. You would have to use a macro to unmerge. This one worked for me:

Sub unmerger()

Dim rReference As Range
Dim rIterator As Range


Set rReference = ActiveSheet.Range("A1:A20000")


For Each rIterator In rReference

If rIterator.MergeCells = True Then

rIterator.MergeCells = False

If rIterator.Offset(1, 0).Value = "" Then

rIterator.Offset(1, 0).Value = rIterator.Value
End If

End If


Next rIterator


End Sub
0
 
roger_karamCommented:
oh, and after unmerging use a Pivot Table with the values you want to see in Column - this will show both values while using vlookup will still only return the first.

Sincerely,
RK
0
 
nts42aAuthor Commented:
Well, that was a start. It did unmerge the cells, and copied some of the data to blank cells. However, it leaves a significant number empty. Example-2.xlsx
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
nts42aConnect With a Mentor Author Commented:
I was able to find a solution to this last question from a previous thread (Title: if null excel.) Thanks - matthewspatrick. I will accept multiple solutions and award points. Then I will post the next question if I am unable to find a previous thread dealing with the multiple data returns.
0
 
nts42aAuthor Commented:
I would also like to thank matthewspatrick for "Title if null excel"
0
 
roger_karamCommented:
Thanks for the points nts42a! Sorry i missed the blank cells.

sincerely,
RK
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.