How? Report on pricing percentages
Posted by Chas on November 11, 2000 3:53 PM
I have thousands of items on a sheet basically like
the follwing:
Item Store Price
Ball A 2
Ball B 3
Ball C 3.5
Apple D 1
Apple E 1.5
Apple C 1.87
Cards A 1.75
Cards G 0.98
Cards D 1.25
I would like to find a way to select by rows, everything
that store A sells and then select all rows of the other
stores that are selling the same item. ex: All Balls and
all Cards would be selected with the idea of comparing
prices between stores per item. I will then copy all
selected to a new sheet and add a column where I can show
how much higher priced 2 of the stores are than the 1 by
percentages of the low price. It takes about 4 hours to
do it manually now and I have to believe there is a
better way through formulas or macros. Any help on this
is greatly appreciated. Thanks in advance,
Chas
Posted by marbel on November 11, 2000 5:27 PM
This sounds like a perfect PIVOT TABLE opportunity. Highlight your data table (with field names, but no blank rows or columns to break up the table), choose Data/Pivot Table from the menu, and experiment with your options. I did a quickie on your data with Item as a row field, Store as a column field, and Price as a data field, showing . You can call the results from another sheet by the sort of tricky "GETPIVOTDATA" function, but the calculated field and item options may allow you to do your analysis right within the table.
Read: "Custom Calculations for PivotTable Data Fields" if you want % of base item, which might make sense for you to determine where the odd pricing is.
Posted by Celia on November 11, 2000 8:22 PM
Chas
Marbel's suggestion of a pivot table is probably the best solution, but here's an alternative :-
1.Put in D1 the formula =IF($B1="A",$A1,"")
Fill the formula down the column.
This should put all the store A items(for store A only) in column D.
2.Put in E1 the formula =IF(ISNA(VLOOKUP($A1,$D$1:$D$10000,1,FALSE)),"",$A1)
Fill the formula down the column.
This should put all the store A items(for all stores) in column E.
3.Select columns D:E and Copy>PasteSpecial>Values
4.Sort by column E then by Column C.
5.Select the rows with data in column E, Copy and Paste to a new sheet. (The new sheet should now have data in columns A:E).
6.Run the macro(below) in the new sheet. The macro should put the price comparison figures in column D.
7.Delete columns D:E from the original sheet.
Sub PriceComparison()
Dim c As Range, rng As Range
Set rng = Intersect(ActiveSheet.UsedRange, Range("E1:E65536"))
MsgBox rng.Address
Application.ScreenUpdating = False
For Each c In rng
If c.Row = rng.Cells(1).Row Then
c.Offset(0, 1).Value = 1
c.Offset(0, 2).Value = c.Offset(0, -2).Value
Else
If c.Value <> c.Offset(-1, 0).Value Then
c.Offset(0, 1).Value = 1
c.Offset(0, 2).Value = c.Offset(0, -2).Value
Else
c.Offset(0, 2).Value = c.Offset(-1, 2).Value
c.Offset(0, 1).Value = c.Offset(0, -2).Value / c.Offset(0, 2).Value
End If
End If
Next
Range("D:E,G:G").Delete
End Sub
Celia
Posted by Chas on November 13, 2000 4:12 AM
Thank you both for your help. I have tried many variations of the pivot table and I can't get it to give me the report that I can use. Celia's plan is very close and probably perfect but I don't understand enough about the code in the macro to adapt it to my sheet. I have many more columns of data that go along with the pricing and can't seem to modify the code to work properly. The selection process will be speeded up considerably though using your formulas. I am still working at it though as it looks like the answer.
Again, Thank you both very much,
Chuck
Posted by Celia on November 13, 2000 8:04 AM
Chuck
If you want, send me your file and I'll adjust the macro.
Celia
