• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Converting a table to a list

VikasT

New Member
I have a table of sales transactions that I need to convert to a list.

The file is attached. Each Row represents a specific date. The first three columns are Date, Salesperson and Location. Then there are three more columns for each of the five products being sold. Each product has a Quantity, Price and Value Column.

I'd like instead an output that gives me

Date Salesperson Location Product Quantity Price Value

The descriptions I've found on the web are either in VBA - that doesn't match up correctly or describe a set of menu options from Excel 2007 that I can't find (I'm using 2010).

It feels like I should be able to do this with a pivot table, but I can't figure out how.

Thank you!

Vikas
 

Attachments

  • Table to list.xlsx
    12.8 KB · Views: 11
Hi Vikas ,

What is supposed to happen if there are 2 or more entries in the same row , as is the case in row 20 and row 33 ?

Is the value in column Y the price or the price multiplied by the quantity ?

Narayan
 
Hi Vikas ,

What is supposed to happen if there are 2 or more entries in the same row , as is the case in row 20 and row 33 ?

Is the value in column Y the price or the price multiplied by the quantity ?

Narayan


Narayan,

If there are two entries then I need it to convert to two lines in the list, so Row 20 would become:

4/16/2013 James Indiana Soap 3 25

AND

4/6/2013 James Indiana Shampoo 1 20

Column Y is just the price.

Thanks,

Vikas
 
Hi Vikas ,

Is VBA acceptable ?

Narayan

Narayan,

I need to solve this problem and I'm thankful for any way you can help me do that.

That said, VBA is ok. I'm not that familiar with VBA, so it is harder for me to modify (if/when I need to), so I prefer native formulas where I can usually figure out how someone has worked their magic!

Vikas "I need to take several Chandoo classes :) " T
 
Dear VikasT
You can reconstruct the data inserting a column after location that will be Column D. In this column you can keep the Product name and prepare a PivotTable. You will get your required output.
 
Dear VikasT
You can reconstruct the data inserting a column after location that will be Column D. In this column you can keep the Product name and prepare a PivotTable. You will get your required output.

Nazmul,

I'm not sure I understand. I think you're saying that I should make a new column which would go after "Location". This new column would be Column "D".

Where I get lost is when you suggest I put in the Product Name into Column D. Are you suggesting that I do that by hand? That's not going to be practical because my actual dataset is thousands of rows and a few hundred columns.

Because of the size, I also cannot use the brute force method of taking each row and spawning multiple versions to do lookups from. There are just too many products.

If you're suggesting something different, then please show me in the excel file so I can see what you're thinking.

Thanks,

Vikas
 
Hi Vikas,

An absolute perfect solution by @NARAYANK991 Sir.

See the attached file for my approach towards the problem through formulas. Solution uses 3 Helper columns (yellow colour). The formulas uses OFFSET a number of time, so can't tell you the performace on your real data set.

The file also had Narayan Sir solution.

Regards,
 

Attachments

  • Table_to_list(1).xlsm
    27.8 KB · Views: 3
Hi Vikas ,

See this file.

Narayan

Narayan,

Works like a charm. My actual file has several thousand rows and a few hundred products, so this is quite a timesaver. Thank you very much - especially for your documentation within the code so I could make the few modifications I needed to.

Vikas
 
Back
Top