"Lookup" when value occurs multiple times

S

ShaanB

New Member
Joined
Mar 27, 2015
Messages
35
Hi all,

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

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

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
 
Sort by date Sort by votes
ShaanB said:
Hi all,

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

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

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
Click to expand...


Hello

Try this out

Code: 
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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="width: 77"]Style/Color[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="width: 78"]Style Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]123456-000[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="width: 78"]Men's Jersey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]123456-000[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="width: 78"]Men's Jersey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]123456-000[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="width: 78"]Men's Jersey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]123456-000[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="width: 78"]Men's Jersey[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
mohsinbipu
  • Solved
Count the occurrence with the array formula
Replies
2
Views
682
Fluff
Fluff
E
  • Solved
Lookup value from range when name matches AND date falls within date range
Replies
4
Views
2K
Alex Blakenburg
A
P
  • Solved
VBA to send multiple emails, run time error 440 after ~30 emails
Replies
5
Views
2K
Domenic
D
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
1K
paulkc
P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top