Merging Data Without Key

[mbespartochnyy]

Hello everyone,

I'm working with a data set that is in unusual format (see Sheet1 in attached spreadsheet). I am trying to load Data from Sheet1 and make it look like data in Sheet2.

I am able to load Reference (Column B) through Check number (Column N), but I'm having trouble bringing Vendor, Company Code, Name, and City fields which are above each set of data.

Attached is a QVW that I have so far. It Brings in the data from Columns B-N and list of Vendors, Company Codes, Vendor Names, and Cities from the data set but doesn't link the two tables.

The rules of data is that a table with data will always follow supplier info.

Does anyone have any suggestions how this data can be transformed and joined?

Thanks,

Mikhail Bespartochnyy

[ahaahaaha]

Hi Mikhail,

As an variant, an example in the attached file.

Result

Regards,

Andrey

[mbespartochnyy]

Thanks, Andrey! That solution works for a small data set, however, I'm working with a spreadsheet with close to 1,000,000 lines. A client that I'm working with is expected to provide additional spreadsheets with about 200,000 lines each month. I was hoping there might be a more automated way to process and load the data.

Mikhail B.

[sunny_talwar · Accepted solution]

Check if the attached is automated enough

[mbespartochnyy]

Forgot to mark this correct but this did the trick! It worked perfectly on the entire data set. Thanks, Sunny!

Mikhail B.