Need Macro to sort thru raw data

Closed
Karminak - Jun 3, 2009 at 11:06 AM
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 - Jun 7, 2009 at 11:59 PM
Hello,
I have a POS data file that I'm not sure how to approach. The data columns are not specific and the data loops across a few rows. What I need to show is the Qty Sold in Eaches and Dollars for every SKU at different stores. Below is a small snip of the data showing the SKU (190724), type ($ or ea), store #(7002, 7004, 7030, etc.) and qty (0.01) looping horizontally for each SKU. I have over 100 SKUs and 178 stores so it is quite a mess. Since it is a weekly report, there has to be a way to do this with macros or functions so I don't have to do it manually. If anyone is will to take this challenge and help me, I will greatly appreciate it.

Thanks in advance,
Karminak


NEP SKU QTY TYPE TYPE Store # Qty Store # Qty Store # Qty
LIN05 ZA01 SDQ01 SDQ03 SDQ04 SDQ05 SDQ06 SDQ07 SDQ08
190724 QtySold EACH 7002 0.01 7004 0.01 7030 0.01
190724 QtySold DOLLARS 7002 0.01 7004 0.01 7030 0.01
190724 QtySold EACH 7161 0.01 7167 0.01 7180 0.01
190724 QtySold DOLLARS 7161 0.01 7167 0.01 7180 0.01
190724 QtyEnding Bal EACH 7002 14 7004 3 7030 3
190724 QtyEnding Bal EACH 7161 1 7167 1 7180 1

2 replies

mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 160
Jun 5, 2009 at 02:17 AM
So tell me exactly what you want ????? Mean do you want to add the quantity on different different stores against SKU ????

can you be more specific as you have mentioned below columns

NEPSKU
QTY
TYPE
Store#
Qty
Store#
Qty
Store#
Qty

but you didn't tell whats your needs ??????

It will be better to explain Quantity Sold & quality ending that in which column they lies ????


0
Hello,

Thanks for your response. This is a data file showing the sales of items at various stores. What I need to do is pull the data associated with the QTY Sold by eaches and dollars for each SKU by store. So for SKU 190724, store# 7002 sold 5 items each for $10 dollars, store# 7004 sold 5 items each for $15 dollars, etc.

I need to sort the data into a easily readable format that can be manipulated.

Thanks in advance,
Karminak


NEP SKU QTYTYPE TYPE Store# Qty Store# Qty Store# Qty
LIN05 ZA01 SDQ01 SDQ03 SDQ04 SDQ05 SDQ06 SDQ07 SDQ08
190724 QtySold EACH 7002 5 7004 5 7030 5
190724 QtySold DOLLARS 7002 10 7004 15 7030 10
190724 QtySold EACH 7161 0.01 7167 0.01 7180 0.01
190724 QtySold DOLLARS 7161 0.01 7167 0.01 7180 0.01
190724 QtyEnding Bal EACH 7002 14 7004 3 7030 3
190724 QtyEnding Bal EACH 7161 1 7167 1 7180 1

Configuration: Windows XP
MS Excel 2003
Firefox 3.0.10
0
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 160
Jun 7, 2009 at 11:59 PM
In this case Pivot Tables are the best thing to do ..... you can get info about Pivot from the net ....
0