Extracting multiple entries in a single merged cell to individual row

[OlekOleh]

Hell everyone!

This is my first time posting on this forum, but I have a fairly basic question.

I have received a data dump with multiple values in a single merged cell that I need to extract into individual rows so I can preform a vlookup on this data.

It looks like this expect on a much larger scale:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Value[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]x1
x2
x3
x4
x5
[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]y1[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]z1
z2[/TD]
[TD]Pending[/TD]
[/TR]
</tbody>[/TABLE]

What I need is to find a way to extract the "Value" column and "Status" column without having to manually go through and copy and past each value. Basically I need to get the table below:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Value[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]x1[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]x2[/TD]
[TD]Good
[/TD]
[/TR]
[TR]
[TD]x3[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]x4[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]x5[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]y1[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]z1[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD]z2[/TD]
[TD]Pending[/TD]
[/TR]
</tbody>[/TABLE]


I have tried looking around but have not found a solution to my problem. If anyone has any advice that would be greatly appreciated.

Have a great day!

 

[Derek73]

Try running the UnMergeFill Macro below:

Sub UnMergeFill()


Dim lr As Long, lc As Long
    lr = LastRowWithData
    lc = LastColWithData
    Range(Cells(1, 1), Cells(lr, lc)).Select
    Selection.UnMerge
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
    Application.CutCopyMode = False
    Range(Cells(1, 1), Cells(lr, lc)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False


    
End Sub
Public Function LastRowWithData() As Long
Dim ExcelLastCell As Variant
Dim Row As Long


Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
Row = ExcelLastCell.Row
MsgBox (Row)
Do While Application.CountA(ActiveSheet.Rows(Row)) = 0 And Row <> 1
    Row = Row - 1
Loop
LastRowWithData = Row
End Function


Public Function LastColWithData() As Long
Dim ExcelLastCell As Variant
Dim Col As Long


Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
Col = ExcelLastCell.Column
Do While Columns(Col).Cells.SpecialCells(xlCellTypeConstants).Count = 0 And Col <> 1
    Col = Col - 1
Loop
LastColWithData = Col
End Function

 

[godsaaint]

Hello, no need to extract the entries, you can perform a vlookup without doing it. I'm gonna use index/match instead of vlookup but it's the same result:

Assuming Value is in Column A, Status in Column B, and the lookup value in Column C

D2: =INDEX($B$2:$B$9,MATCH("*"&C2&"*",$A$2:$A$9,0))

Fill down as needed. Be careful with this one because if the lookup-value cell is empty, the formula is going to return "Good" even though is incorrect. If you would like this to avoid this altogether, just this formula instead: =IF(C2="","",INDEX($B$2:$B$9,MATCH("*"&C2&"*",$A$2:$A$9,0)))

 

[horrellbt01]

This wont entirely solve the issue, but if you turn the original data into a pivot table, then double click on the Grand Totals cell, it will turn this into a table that will be easier to edit. From there you could drag the Status down to fill in the blanks.

Example

Pivot Table: Rows=Value
Columns=Status
Values=Count of Status
Report Layout = Compact
Grand Totals = on for rows and columns

Double Click on bottom right cell

 

[OlekOleh]

Hey, thanks for the easy solution! It works great so far, but I have a small problem I didn't explain. If I have duplicate values in Column A when I do the vlookup I get the first result from Column A that matches. Is there a way to make the vlookup or index/match to return each status in column "B"?

 

[admiral100]

Hi,

Why not just copy the entire column A to C
and enter in D2 the formula

=IF(LEFT(C2,1)="x","Good",IF(LEFT(C2,1)="y","Bad","Pending"))

 

[godsaaint]

Hey, thanks for the easy solution! It works great so far, but I have a small problem I didn't explain. If I have duplicate values in Column A when I do the vlookup I get the first result from Column A that matches. Is there a way to make the vlookup or index/match to return each status in column "B"?

So for example, in A2, you have x1,x2,xe,x4,x5 with a "Good" Status, but is possible to find another x3 in let's say cell A15, but with a different status, like "Pending", correct?

Do you want to return all the status for each specific value in the same cell?

 

[OlekOleh]

This would work if x1=x2=x3 and so one but each value in column A is unique. Thanks for the help though!

 

[OlekOleh]

Yes that is exactly what I meant! It doesn't matter to me if the status for a specific value is in the same cell. I would imagine it would be easier to just write some kind of index/match or vlookup in the adjacent cell and then after just review each status to my value to see which is correct.

And thanks for the help! This is asking a lot but would you know any good resources for learning more on excel, and VBA? I am just getting into this and would like to dedicate a portion of my time to really learn the details of excel so I can filter through data easier.

 

[admiral100]

OK

In C2 enter =A2 and drag down

in D2 =LOOKUP(1,1/($B$2:B2>0),$B$2:B2) and drag down