how to check if all prices are the same

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
769
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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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.
 

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
769
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.
 

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
769
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>
 

Sam_D_Ben

Active Member
Joined
Oct 17, 2012
Messages
381
Please see if this is what you are looking for.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(102,0,0)"><colgroup><col width="25px" style="background-color: rgb(255,255,255)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(255,255,255);text-align: center;color: rgb(102,0,0)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(102,0,0);text-align: center;">1</td><td style="text-align: center;;">Weight</td><td style="text-align: center;;">Price</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">2</td><td style="text-align: center;;">5</td><td style="text-align: center;;">7.49</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;background-color: #D6DCE4;;">3</td><td style="font-weight: bold;text-align: center;background-color: #D6DCE4;;">5</td><td style="font-weight: bold;text-align: center;background-color: #D6DCE4;;">10</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">3</td><td style="text-align: center;;">5</td><td style="text-align: center;;">7.49</td><td style="text-align: center;;"></td><td style="text-align: center;;">4.99</td><td style="text-align: center;;">7.49</td><td style="text-align: center;;">8.99</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">4</td><td style="text-align: center;;">10</td><td style="text-align: center;;">8.99</td><td style="text-align: center;;"></td><td style="text-align: center;;">7.49</td><td style="text-align: center;;">7.49</td><td style="text-align: center;;">8.99</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4.99</td><td style="text-align: center;;"></td><td style="text-align: center;;">1.25</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">7.49</td><td style="text-align: center;;"></td><td style="text-align: center;;">4.99</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">7</td><td style="text-align: center;;">10</td><td style="text-align: center;;">8.99</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">8</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1.25</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">9</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4.99</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(102,0,0);border-top:none;text-align: center;background-color: rgb(255,255,255);color: rgb(102,0,0)">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(102,0,0)"><thead><tr style=" background-color: rgb(255,255,255);color: rgb(102,0,0)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(255,255,255);color: rgb(102,0,0)">D3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$2:$B$9, SMALL(<font color="Green">IF(<font color="Purple">(<font color="Teal">$D$2=$A$2:$A$9</font>), MATCH(<font color="Teal">ROW(<font color="#FF00FF">$A$2:$A$9</font>), ROW(<font color="#FF00FF">$A$2:$A$9</font>)</font>), ""</font>),ROWS(<font color="Purple">$A$2:A2</font>)</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(255,255,255);color: rgb(102,0,0)">E3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$2:$B$9, SMALL(<font color="Green">IF(<font color="Purple">(<font color="Teal">$E$2=$A$2:$A$9</font>), MATCH(<font color="Teal">ROW(<font color="#FF00FF">$A$2:$A$9</font>), ROW(<font color="#FF00FF">$A$2:$A$9</font>)</font>), ""</font>),ROWS(<font color="Purple">$A$2:A2</font>)</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(255,255,255);color: rgb(102,0,0)">F3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$2:$B$9, SMALL(<font color="Green">IF(<font color="Purple">(<font color="Teal">$F$2=$A$2:$A$9</font>), MATCH(<font color="Teal">ROW(<font color="#FF00FF">$A$2:$A$9</font>), ROW(<font color="#FF00FF">$A$2:$A$9</font>)</font>), ""</font>),ROWS(<font color="Purple">$A$2:A2</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,522
Office Version
2013, 2007
Platform
Windows
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
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,646
Office Version
365
Platform
Windows
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
 

Sam_D_Ben

Active Member
Joined
Oct 17, 2012
Messages
381
Yeah i dint notice it. Like Gerald and Peter, thats super easy. I checked it.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(102,0,0)"><colgroup><col width="25px" style="background-color: rgb(255,255,255)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(255,255,255);text-align: center;color: rgb(102,0,0)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(102,0,0);text-align: center;">1</td><td style="text-align: center;;">Weight</td><td style="text-align: center;;">Price</td><td style="text-align: center;;"></td><td style=";">Find Missings</td><td style=";">Count</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">2</td><td style="text-align: center;;">5</td><td style="text-align: center;;">7.49</td><td style="text-align: center;;"></td><td style=";">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">3</td><td style="text-align: center;;">5</td><td style="text-align: center;;">7.49</td><td style="text-align: center;;"></td><td style="text-align: right;;">1.25</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">4</td><td style="text-align: center;;">10</td><td style="text-align: center;;">8.99</td><td style="text-align: center;;"></td><td style="text-align: right;;">4.99</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4.99</td><td style="text-align: center;;"></td><td style="text-align: right;;">7.49</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">7.49</td><td style="text-align: center;;"></td><td style=";">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">7</td><td style="text-align: center;;">10</td><td style="text-align: center;;">8.99</td><td style="text-align: center;;"></td><td style="text-align: right;;">7.49</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">8</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1.25</td><td style="text-align: center;;"></td><td style=";">10</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">9</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4.99</td><td style="text-align: center;;"></td><td style="text-align: right;;">8.99</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(102,0,0);border-top:none;text-align: center;background-color: rgb(255,255,255);color: rgb(102,0,0)">Sheet5</p><br /><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,824
Members
406,500
Latest member
Tknotmaxx

This Week's Hot Topics

Top