how to check if all prices are the same

[bigdan]

I have 1000 rows of text which includes the Province, weight classes, prices, etc. All the text looks like this:
[TABLE="width: 238"]
<tbody>[TR]
[TD="width: 238"]ON_XY_DOH|PARCEL|30|40.99[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Gerald Higgins]

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]

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.

 

[Sam_D_Ben]

Could you please post some sample data with the error result.

 

[bigdan]

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.




[TABLE="width: 500"]
<tbody>[TR]
[TD]5[/TD]
[TD]7.49[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]8.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]7.49[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]8.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4.99[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Sam_D_Ben]

Please see if this is what you are looking for.

Excel 2013/2016
	A	B	C	D	E	F
1	Weight	Price
2	5	7.49		3	5	10
3	5	7.49		4.99	7.49	8.99
4	10	8.99		7.49	7.49	8.99
5	3	4.99		1.25
6	3	7.49		4.99
7	10	8.99
8	3	1.25
9	3	4.99
Sheet5

Cell Formulas
Range	Formula
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.

 

[jolivanes]

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.

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]

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.



[TABLE="width: 92"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

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
	A	B	C	D	E	F	G
1	Weight	Price			Row Labels	Count of Price
2	5	7.49			3	3
3	10	8.99			1.25	1
4	3	4.99			4.99	2
5	5	7.49			5	2
6	10	8.99			7.49	2
7	3	1.25			10	2
8	3	4.99			8.99	2
9					Grand Total	7
10
Check Weight & Price

 

[Sam_D_Ben]

Yeah i dint notice it. Like Gerald and Peter, thats super easy. I checked it.

Excel 2013/2016
	A	B	C	D	E
1	Weight	Price		Find Missings	Count
2	5	7.49		3
3	5	7.49		1.25	1
4	10	8.99		4.99	2
5	3	4.99		7.49	1
6	3	7.49		5
7	10	8.99		7.49	1
8	3	1.25		10
9	3	4.99		8.99	1
Sheet5