• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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
0
nts42a
Asked:
nts42a
  • 3
  • 3
2 Solutions
 
roger_karamCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nts42aAuthor 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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now