Returning a value based on multiple variables

LostDog

New Member
Joined
Apr 23, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi there,

I am trying to tame a massive spreadsheet I inherited in to something usable. Through some hack and slash macros, lookups and beer, I've got it to a point I'm stumped. Essentially, it's a table of components with price and quantity breakdowns but as it's formatted it can't be used easily. If I can format it better I can use a simple pivot table to look up parts and prices based on quantity.

Here is a representation what I've got (the real file is about 3000 rows and 100 or so columns):

PartQty 1Qty 5Qty 12Qty 18Qty 24
5683 RevB$23.00
4984 Rev A$56.00$42.00$37.00
9963 Rev C$98.00
1366 Rev A$115.00$105.00


I'd like to turn it in to this:

Book1
ABC
12PartPriceQuantity
135683 RevB$23.00Qty 5
144984 Rev A$56.00Qty 1
154984 Rev A$42.00Qty 12
164984 Rev A$37.00Qty 24
179963 Rev C$98.00Qty 18
181366 Rev A$115.00Qty 5
191366 Rev A$105.00Qty 24
Sheet1


I have it in this format:

Book1
ABCDEFGHIJ
1PartPriceQuantityPartQty 1Qty 5Qty 12Qty 18Qty 24
25683 RevB$23.005683 RevB$23.00
34984 Rev A$56.004984 Rev A$56.00$42.00$37.00
44984 Rev A$42.009963 Rev C$98.00
54984 Rev A$37.001366 Rev A$115.00$105.00
69963 Rev C$98.00
71366 Rev A$115.00
81366 Rev A$105.00
Sheet1


Looking at the logic, I somehow need to evaluate "if part is 5683 Rev B AND price is $23.00" then paste "Qty 5" in to C2. It seems like it should be easy to cross reference Part and Price then return the column header but I'm lost. I've played with index(match) and other techniques but nothing has worked. There is no set pattern in the quantity / price relationship so it needs to be done off their values.

I'm sure I'm missing a simple solution but I just can't see it!

Thank you much!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,760
Office Version
  1. 365
Platform
  1. Windows
If you willing to look at a Power Query solution its a pretty easy PQ Transformation.

If you want to try it out.
Start at right back at your original table "real file"

Convert it into a table. To do that, click anywhere inside the table and then, either:-
  • Insert > Table OR
  • Ctrl + T
  • Once you have done that go to Table Design > in the far left corner under file change the Table Name by pasting in tbl_PartQtyPrice
Now while still being in the table
  • Data > From Table/Range (posittioned far left just under the File TAB)
  • You are now in Power Query - Home > Advanced Editor (far left just under the Tranform button)
  • Replace all the code you see there with the Code below.
  • Now under Home if you click Close & Load it will save it to a new worksheet in your workbook.
PS: Splitting Qty out into the word and the number just a couple of clicks away if you wanted to do that as well.

The replacement code is this:
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="tbl_PartQtyPrice"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Part"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "Price"}, {"Attribute", "Quantity"}}),
    #"Changed Type Price" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Price", Currency.Type}}),
    #"Reordered Columns Price Qty" = Table.ReorderColumns(#"Changed Type Price",{"Part", "Price", "Quantity"})
in
    #"Reordered Columns Price Qty"
 
Solution

LostDog

New Member
Joined
Apr 23, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
If you willing to look at a Power Query solution its a pretty easy PQ Transformation.

If you want to try it out.
Start at right back at your original table "real file"

Thanks much for the suggestion! I've never looked at Power Query so I'll play with it this weekend and let you know how it goes!
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,760
Office Version
  1. 365
Platform
  1. Windows
Let me know how you go.
One tip, when you unpivot highlight what you want to keep and select Unpivot Other Columns. PQ effectively hard codes the column names, and what you want to keep is unlikely to change while the columns you want to unpivot are likely to expand, so you don't want them to be hard coded.
 

LostDog

New Member
Joined
Apr 23, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Let me know how you go.
One tip, when you unpivot highlight what you want to keep and select Unpivot Other Columns. PQ effectively hard codes the column names, and what you want to keep is unlikely to change while the columns you want to unpivot are likely to expand, so you don't want them to be hard coded.

Hi there,

I've given this a go and here was the result (I used it on both my "live" file and the example in the post above). Both instances had the same type of result:

Book1
ABC
1PartPriceQuantity
25683 RevB23Price
35683 RevBPart2
45683 RevB23Qty 5
54984 Rev A56Price
64984 Rev APart2
74984 Rev A56Qty 1
84984 Rev A42Qty 12
94984 Rev A37Qty 24
104984 Rev A42Price
114984 Rev APart2
124984 Rev A98Qty 18
134984 Rev A37Price
144984 Rev APart2
154984 Rev A115Qty 5
164984 Rev A105Qty 24
179963 Rev C98Price
181366 Rev A115Price
191366 Rev A105Price
Sheet2


I noticed that having two columns the same name in the original file changed one of them to keep them unique (the second "Part" column became "Part2").

Book1
ABCDEFGHIJ
1PartPriceQuantityColumn1Part2Qty 1Qty 5Qty 12Qty 18Qty 24
25683 RevB$ 23.005683 RevB$ 23.00
34984 Rev A$ 56.004984 Rev A$ 56.00$ 42.00$ 37.00
44984 Rev A$ 42.009963 Rev C$ 98.00
54984 Rev A$ 37.001366 Rev A$ 115.00$ 105.00
69963 Rev C$ 98.00
71366 Rev A$ 115.00
81366 Rev A$ 105.00
Sheet1


Could this have caused an issue?

I have to say, you have opened my eyes to Power Query and even Tables. I had no idea of the usage. I've got lots of learning to do!

Thanks much for the help!
 

LostDog

New Member
Joined
Apr 23, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hmm... Can't I edit a reply?

I see my mistake! I was thinking in terms of a standard excel sheet and thinking this was copying from one side to the other. It's a direct transformation of my raw data rather than copying the data from one side to the other...

I *think* it's working! I'm playing some more then will hit "solved" if it worked.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,760
Office Version
  1. 365
Platform
  1. Windows
I have to say, you have opened my eyes to Power Query and even Tables. I had no idea of the usage. I've got lots of learning to do!

Loved your commentary, I am imagining your eyes lighting up to the new possibilities.

Just an additional watch out.
After the Source step, Power Query automatically puts in a Changed Type step.
Unlike formatting a cell changing the data type does change the contents of a column. So if it guessed it to be Int (Integer) and you later find out you need decimals, changing the data type to decimal in a later Changed Type will not recover data already lost in the first Changed Type step.

Also the first Changed Type hard codes the columns, which is ok in most cases but not in an unpivot scenario where the number of columns may expand.

In my version I,
  • Deleted the automatic changed type step
  • waited until I unpivoted the data, so I now only had 1 column that was always going to be there to format
  • formatted it to currency (which allows up to 4 decimal places)
Hope you enjoy using PQ and thanks for the feedback.
 

Forum statistics

Threads
1,141,818
Messages
5,708,763
Members
421,588
Latest member
Wawie

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
Top