Forum Discussion
Extract Costs From Post code.
I have a large dataset, which I have cleansed to show only Scotland data. I have then further broken this down by postcode to show only a "Post code stub" and then another "4-digit stub" column - this is all the digits and letters before the space (UK postcode format). On the "Data" tab is also a column called 'Total HUs'. This relates to the number of pallets being delivered to a particular postcode.
I then have another tab called "Rate Card". This gives all the rates based on the 'Total HUs' and the 'Post code' stubs. Multiple postcode stubs are listed with the same rate and this is all on one like 'G,EH,PA1-19,FK'. Another obstacle is, one postcode stub eg AB, can have more than one price. This is where "4-digit stub" comes into is. For example AB1-25 is one cost and then AB26+ is a different cost
In short, I need some formula/function, that'll do the following:
1) Check number of "Total HUs"
2) Lookup the "Post Code Stub"
3) Then "4 digit stub" to see if different price occurs
4) Return the correct rate
Sometimes, the data needs to be cleaned up to where reasonable formulas can be written. In the attached workbook, see the rows and columns in red on worksheet RateCard2 where I did that.
Row 2 contains the highest quantity for each respective range in row 1. Increase the 999 if you deem it necessary.
I split the rows of rates as needed to get the Post code stubs separate from the 4-digit stubs. Those two types have different values for columns C and D.
The reason for adding leading and trailing commas is because the G Post code stub could inappropriately match the "G" in DG. (That's poor code design. But there might be a historical reason for it.)
Then you can use the formula:
=LET( HU_rounded, ROUND(A2,0), stub_suffix, VALUE( MID(C2, LEN(B2)+1, 2) ), rate_col, XMATCH( HU_rounded, RateCard2!$G$2:$L$2, 1), rate_row, FILTER( RateCard2!$G$4:$L$14, ISNUMBER(SEARCH( ","&B2&",", RateCard2!$B$4:$B$14 ) ) * (RateCard2!$C$4:$C$14 <= stub_suffix) * (RateCard2!$D$4:$D$14 >= stub_suffix), "not found" ), IFERROR( TAKE( DROP(rate_row,0,rate_col-1), 1, 1 ), "not found" ) )Because of the TAKE and DROP functions, this requires Excel 365 or Excel for the web.
Sometimes, the data needs to be cleaned up to where reasonable formulas can be written. In the attached workbook, see the rows and columns in red on worksheet RateCard2 where I did that.
Row 2 contains the highest quantity for each respective range in row 1. Increase the 999 if you deem it necessary.
I split the rows of rates as needed to get the Post code stubs separate from the 4-digit stubs. Those two types have different values for columns C and D.
The reason for adding leading and trailing commas is because the G Post code stub could inappropriately match the "G" in DG. (That's poor code design. But there might be a historical reason for it.)
Then you can use the formula:
=LET( HU_rounded, ROUND(A2,0), stub_suffix, VALUE( MID(C2, LEN(B2)+1, 2) ), rate_col, XMATCH( HU_rounded, RateCard2!$G$2:$L$2, 1), rate_row, FILTER( RateCard2!$G$4:$L$14, ISNUMBER(SEARCH( ","&B2&",", RateCard2!$B$4:$B$14 ) ) * (RateCard2!$C$4:$C$14 <= stub_suffix) * (RateCard2!$D$4:$D$14 >= stub_suffix), "not found" ), IFERROR( TAKE( DROP(rate_row,0,rate_col-1), 1, 1 ), "not found" ) )Because of the TAKE and DROP functions, this requires Excel 365 or Excel for the web.
- Thank you, I couldn't figure out a way to do this with a formula. I managed to complete it via Power Query. It's nice to get a different solution and a formulaic one!
