Rearranging columns to rows

**joshuar** · 01-07-2010, 05:59 AM

Hi All,

I have a problem here.
I have a table with the following data- Supplier name, Item name, Qty. purchased for Outlets, Total Qty.for all outlets, Rate, Amount

I would like to arrange the data such that only the outlets for which there are purchases will appear along with the company name, item name, quantity, rate & amount. i.e. if there are no purchases for an outlet that data would not appear. The quantity to be displayed must be for one outlet only

The attached file will be more illustrative. Hope I am clear.

Regards,

**contaminated** · 01-07-2010, 06:47 AM

There were several items in your output, but my formula gave all instances (purchased items)

In A21

Please Login or Register  to view this content.

In B21 (Confirm w/ Ctrl + Shift + Enter)

Please Login or Register  to view this content.

In C21 (Confirm w/ Ctrl + Shift + Enter)

Please Login or Register  to view this content.

In D21 (Confirm w/ Ctrl + Shift + Enter)

Please Login or Register  to view this content.

In E21

Please Login or Register  to view this content.

I've attached a sample so can see what I've done.

Hope it helps

**joshuar** · 01-10-2010, 04:49 AM

Thanks Vasul for your reply and sorry again for the delay.
I did a check of the formulas suggested on the original file.
The formula does work in most cases but in some cases they return a numeric error.
The cause for this seems to be that same items are purchased from different vendors i.e. column B will have items repeated. eg. Milk may be purchased also from supplier JKL.

Could you suggest something to rectify this error. Also can this kind of sheet be tackled using pivot tables?

Regards,
J

**DonkeyOte** · 01-10-2010, 05:10 AM

See attached - slight variation from prior sugggestion.

Regards your question on use of PT - that would require you restructure your data table - this could be done but it's perhaps just as easy to use formulae in this instance.