Consolidate information in each row of database

**maacmaac** · 09-30-2011, 02:22 PM

I am trying to consolidate information in a database that is not very user friendly.

Column A contains a list of products. Columns B1:Q1 contains a list of invoice numbers.

For each product, I want to show/list all the invoices that contain the product in question. For example, apples were sold on invoices 33333, 77777, and 4. I want to list on another sheet:

Cell A1 = Apples
Cell B1 = 33333
Cell C1 = 77777
Cell D1 = 4

For oranges it would be
Cell A2 = Oranges
Cell B2 = 11111
Cell C2 = 55555
Cell D2 = 7

I have attached a small sample of raw data file and desired output. Thanks in advance for any comments.

**watersev** · 09-30-2011, 03:26 PM

hi, maacmaac, please check attachment, run code "test"

**maacmaac** · 09-30-2011, 05:56 PM

The code works fine for sample I sent but the raw data file is actually 1334 columns wide.

Please Login or Register  to view this content.

It appears to be picking up everything up to column 1296 but nothing after that column. I tried to force the column by changing one line of code from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

That didn't work either. I can't figure out why it won't pick up any data after column 1296. I have attached another sample with 1334 columns of data. Thanks.