how to check if all prices are the same

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
840
Office Version
  1. 2013
Platform
  1. Windows
I have 1000 rows of text which includes the Province, weight classes, prices, etc. All the text looks like this:
ON_XY_DOH|PARCEL|30|40.99

<tbody>
</tbody>

In this case the last two numbers are what matter. This means that if shipping weights are up to 30 pounds we charge 40.99

So there's 1000 rows of text like this, so I made a number of mid(x,y,z) statements so that all I have now is 1000 rows of 2 cells, which would say the weight and the shipping cost.

How do I make sure that for every mention of 30 the rate given is 40.99, and same for all the other shipping weights? There's about 50 weight classes, and I want to make sure the rate is the same for each weight class.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
A pivot table could do this quite quickly.

Every entry for weight class should only have one entry for price, and it would be easy to check the pivot table whether or not this was actually the case.

By the way, using mid(x,y,x) functions to break up your data obviously works, but there are other ways which might be better, such as Excel's built in Text to Columns function.
 
Upvote 0
I thought of the pivot table option but then couldn't figure out how to go about it. I put the various weights in the rows - but then the prices in the corresponding column ended up being a sum. I could average it out as well but there was no option to catch prices that didn't match.
 
Upvote 0
Could you please post some sample data with the error result.
 
Upvote 0
There we go. The dataset is actually hundreds of rows but lets say it's this small.

The left side is the weight, the right side is the shipping cost. All 3 pound weights are supposed to be 4.99, all 5 pound weights should be 7.49 etc. But in this case there's a mistake and one of the 3 pounds is 1.25. So I'd like see all the prices mentioned for all the weights, so I can easily tell if there's a mistake somewhere.




57.49
108.99
34.99
57.49
108.99
31.25
34.99

<tbody>
</tbody>
 
Upvote 0
Please see if this is what you are looking for.


Excel 2013/2016
ABCDEF
1WeightPrice
257.493510
357.494.997.498.99
4108.997.497.498.99
534.991.25
637.494.99
7108.99
831.25
934.99
Sheet5
Cell Formulas
RangeFormula
D3{=IFERROR(INDEX($B$2:$B$9, SMALL(IF(($D$2=$A$2:$A$9), MATCH(ROW($A$2:$A$9), ROW($A$2:$A$9)), ""),ROWS($A$2:A2))),"")}
E3{=IFERROR(INDEX($B$2:$B$9, SMALL(IF(($E$2=$A$2:$A$9), MATCH(ROW($A$2:$A$9), ROW($A$2:$A$9)), ""),ROWS($A$2:A2))),"")}
F3{=IFERROR(INDEX($B$2:$B$9, SMALL(IF(($F$2=$A$2:$A$9), MATCH(ROW($A$2:$A$9), ROW($A$2:$A$9)), ""),ROWS($A$2:A2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
I don't know if this helps at all but if you just want to fix possible wrong figures, this would do that.
Column A has your data to be checked/fixed starting at row 2 (top cell is the header)
Column L, starting at the 2nd cell again, has the weights and the cells beside that, in column M, the corresponding values for these weights.
If the weights are not found in column L, it will color the cell in column A yellow.
Since it is just a small amount of data, you mentioned a few thousand cells, it does not require a fancy extra fast piece of code.
Change the references as required.

Code:
Sub Maybe()
Dim c As Range, a
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    a = Split(c, "|")
        If WorksheetFunction.CountIf(Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row), a(2)) = 0 Then
            c.Interior.Color = vbYellow
                Else
            c.Value = a(0) & "|" & a(1) & "|" & a(2) & "|" & Columns(12).Find(a(2), , , 1).Offset(, 1)
        End If
    Next c
End Sub
 
Upvote 0
For the pivot table option . . .

Let's say your weight data is called "WEIGHT", and your price data is called "PRICE".

Set up your pivot table as normal, and specify WEIGHT as the first item in the Row Labels box.
Then specify PRICE as the second item in the Row Labels box.

Then view your pivot table, and it will be very obvious that WEIGHT 3 has two entries for PRICE.




<colgroup><col></colgroup><tbody>
</tbody>
 
Upvote 0
Further to Gerald's comments about a pivot table. Here it is for your sample data. Further, you mentioned that the prices ended up being a sum. In the PT below (cols E:F) you will see that I have changed it so that it COUNTS each price. So to confirm what I did
Put Weight then Price into the Rows area and Price also into the Values area and changed the setting in that area to Count, not Sum

Looking at the PT you can easily see that the weight of 3 has two different prices and further, how many of each of those prices there are.

Excel Workbook
ABCDEFG
1WeightPriceRow LabelsCount of Price
257.4933
3108.991.251
434.994.992
557.4952
6108.997.492
731.25102
834.998.992
9Grand Total7
10
Check Weight & Price
 
Upvote 0
Yeah i dint notice it. Like Gerald and Peter, thats super easy. I checked it.


Excel 2013/2016
ABCDE
1WeightPriceFind MissingsCount
257.493
357.491.251
4108.994.992
534.997.491
637.495
7108.997.491
831.2510
934.998.991
Sheet5
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top