Advanced consolidation of rows into table

[joevan1]

Hi there,

I'm trying to consolidate an inventory list that I get sent through into a specific output. Specifically the raw data comes in a list form, with a new row for each additional piece of data for that item. The problem is, the amount of data for each item varies, specifically, the amount of keyword tags each item is assigned, with some items having none and some having up to eight or nine.

However, each item has a 'buy now' row immediately under the price, and therefore each item's information is between cells containing 'buy now', and the number of rows between these varies depending on the number of keyword tags (seven in the case of no tags, and an additional row for each tag).

Essentially I need a macro to consolidate this list into a table, so for example, if the raw data looks like:

0.43335467
Buy Now
Rayon Slacks
Pants
TROUSERS
Seller: ******** (100.0)
Ship From Australia * * * Ship To Australia
Image
0.079572
Buy Now
Black Beanie
Accessories
Seller: ******** (0.0)
Ship From United States * * * Ship To United States
Image

the output could be identified based on the location of the 'buy now' cell, i.e.
* the code (0.43335467) would be 'buy now', offset by -1,
* the listing (Rayon Slacks) would be 'buy now', offset by 1,
* the parent category, (pants) would be 'buy now', offset by 2
* the first subcategory (TROUSERS) would be 'buy now', offset by 3

and so on.

The difficulty is that the number of subcategories varies, but it is relative to the number of cells between 'buy now's, and so for every number of cells, n, above 7, there exist n subcategories to be consolidated.

I know this is all very confusing, so I have attached and example sheet with the desired output.

Thank you so much in advance for any help you can offer.