"Lookup" when value occurs multiple times

[ShaanB]

Hi all,

I have a really annoyingly formatted sheet that I'm trying to extract information from.

Line Item: <colgroup><col></colgroup><tbody> </tbody>	20 <colgroup><col></colgroup><tbody> </tbody>		Order Date:	6/01/17 <colgroup><col></colgroup><tbody> </tbody>
Style/Color: <colgroup><col></colgroup><tbody> </tbody>	123456-000 <colgroup><col></colgroup><tbody> </tbody>		Cancel Date: <colgroup><col></colgroup><tbody> </tbody>	7/01/17 <colgroup><col></colgroup><tbody> </tbody>	Wholesale <colgroup><col></colgroup><tbody> </tbody>	$50.00
Style Name: <colgroup><col></colgroup><tbody> </tbody>	Men's Jersey <colgroup><col></colgroup><tbody> </tbody>		Payment Terms: <colgroup><col></colgroup><tbody> </tbody>	Net 90 <colgroup><col></colgroup><tbody> </tbody>	Suggested Retail <colgroup><col></colgroup><tbody> </tbody>	$100.00 <colgroup><col></colgroup><tbody> </tbody>
Color Description: <colgroup><col></colgroup><tbody> </tbody>	BLUE <colgroup><col></colgroup><tbody> </tbody>
Product Type: <colgroup><col></colgroup><tbody> </tbody>	Apparel <colgroup><col></colgroup><tbody> </tbody>
Size <colgroup><col></colgroup><tbody> </tbody>	UPC <colgroup><col></colgroup><tbody> </tbody>	Quantity <colgroup><col></colgroup><tbody> </tbody>	Confirmed Date <colgroup><col></colgroup><tbody> </tbody>
S <colgroup><col></colgroup><tbody> </tbody>	889456712345	3	1/01/17

<tbody>
</tbody>

The number of rows for each block differs depending on the different sizes being ordered, there's an extra line for each size variation.

What I'm trying to do is extract that Style/Color number and the Style Name into a list of it's own. So every time "Style/Color:" appears in column A, I want the data that is in column B to be added to a list #1 and every time "Style Name:" appears in column A, the data in column B to appear in list #2 (preferably next to list #1 so I can VLOOKUP on it)

The goal is to eventually build all of this into some kind of macro, but I will happily take functions that will achieve the same thing!

Thanks in advance for your help!

Best,
Shaan

 

[Nine Zero]

Hi all,

I have a really annoyingly formatted sheet that I'm trying to extract information from.

Line Item: <tbody> </tbody>	20 <tbody> </tbody>		Order Date:	6/01/17 <tbody> </tbody>
Style/Color: <tbody> </tbody>	123456-000 <tbody> </tbody>		Cancel Date: <tbody> </tbody>	7/01/17 <tbody> </tbody>	Wholesale <tbody> </tbody>	$50.00
Style Name: <tbody> </tbody>	Men's Jersey <tbody> </tbody>		Payment Terms: <tbody> </tbody>	Net 90 <tbody> </tbody>	Suggested Retail <tbody> </tbody>	$100.00 <tbody> </tbody>
Color Description: <tbody> </tbody>	BLUE <tbody> </tbody>
Product Type: <tbody> </tbody>	Apparel <tbody> </tbody>
Size <tbody> </tbody>	UPC <tbody> </tbody>	Quantity <tbody> </tbody>	Confirmed Date <tbody> </tbody>
S <tbody> </tbody>	889456712345	3	1/01/17

<tbody>
</tbody>

The number of rows for each block differs depending on the different sizes being ordered, there's an extra line for each size variation.

What I'm trying to do is extract that Style/Color number and the Style Name into a list of it's own. So every time "Style/Color:" appears in column A, I want the data that is in column B to be added to a list #1 and every time "Style Name:" appears in column A, the data in column B to appear in list #2 (preferably next to list #1 so I can VLOOKUP on it)

The goal is to eventually build all of this into some kind of macro, but I will happily take functions that will achieve the same thing!

Thanks in advance for your help!

Best,
Shaan

Hello

Try this out

Sub Try()


Dim i As Long
Dim k As String
Dim c As String


k = "Style/Color:"
c = "Style Name:"


'Here input the name of the sheet with the "ugly" data
Sheets("Sheet1").Select


For i = 1 To Rows.Count
'These are so that you jump down to next row in a list in sheet 2 in columns A and B A being the "Syle/Color" and B is "Style Name"
LastRow& = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
LastRow2& = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row


If Cells(i, 1).Value = k Then
Sheets("Sheet2").Range("A" & LastRow + 1).Value = Cells(i, 1).Offset(, 1)
End If


If Cells(i, 1).Value = c Then
Sheets("Sheet2").Range("B" & LastRow2 + 1).Value = Cells(i, 1).Offset(, 1)
End If


Next i




End Sub

these were my results in sheet 2 when i copy pasted your data 4 times

Style/Color <tbody> </tbody>	Style Name <tbody> </tbody>
123456-000	Men's Jersey <tbody> </tbody>
123456-000	Men's Jersey <tbody> </tbody>
123456-000	Men's Jersey <tbody> </tbody>
123456-000	Men's Jersey <tbody> </tbody>

<tbody>
</tbody>