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.

 

[Peter_SSs]

If you don't want to use a pivot table, here is another way. Each formula copied down.

Excel Workbook
	A	B	C	D	E	F
1	Weight	Price			Weight	Check
2	5	7.49			3	Error
3	10	8.99			5
4	3	4.99			10
5	5	7.49			15	Error
6	10	8.99
7	3	1.25
8	3	4.99
9	15	7.99
10	15	8.99
11
Check Weight & Price (2)

 

[bigdan]

Thanks!
If you enter these formulas into row 3, how will D4-F4 and D5:D6 get populated the way they seem to be here?

Please see if this is what you are looking for.

Excel 2013/2016

	A	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Weight[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]7.49[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]10[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]7.49[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4.99[/TD]
[TD="align: center"]7.49[/TD]
[TD="align: center"]8.99[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]8.99[/TD]
[TD="align: center"][/TD]
[TD="align: center"]7.49[/TD]
[TD="align: center"]7.49[/TD]
[TD="align: center"]8.99[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4.99[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1.25[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7.49[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4.99[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]8.99[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1.25[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4.99[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]D3[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]E3[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]F3[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[bigdan]

Ahh!! This solved it very well! Thank you Gerald

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"]
<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]

 

[bigdan]

Wonderful. Thank you!

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 tables to the web >> Excel Jeanie HTML 4

 

[Gerald Higgins]

Hth, tftf

 

[Peter_SSs]

Wonderful. Thank you!

You're welcome. Glad you got something you are happy with.