Excel Expert - Can You Help?... :(
Good morning to all my data and Excel experts!!!
I really need your help. I have an Excel sheet (google sheet link here: https://docs.google.com/spreadsheets/d/1Nuyo4IEqC1-nFt4VuN-zt6Fo9vb3F3i3wdRU3VB5V70/edit?usp=sharing)
The top table is the raw data, I have manually transformed it into the bottom table. Is there any way I can do this automatically?
The top table is organised in terms of ranked best supplier to worst. I need it organised in terms of supplier prices for each product.
I'm talking like 100+ products here, so this manual process will be way too long
Any help is MUCH appreciated and in return, I'll help you with marketing or biz stuff in any way I can!
Thank you as always IH's - don't let me down lol
Here's what I would do: I would mix the INDEX and MATCH functions of excel to find the name of the provider and get the value of the cell next to it. Something like this (you would have to fix the range to fit your cells):
=INDEX($A$1:$Z$100,MATCH($A2,A1:A100,0),MATCH(B$1,A1:Z1,0)+1)
If you know how to code, Google AppScript is a great choice as well.
Thank you so much Cjortuzar! Much appreciated.
I think I understand most of it.
Any chance you could explain the formula a little bit, or point me in the direction of some www's for further reading?
Thank you ever so much again!
Explanation of the formula:
INDEX returns the value of a cell in a range based on the row and column numbers.
MATCH finds the position of a value in a range.
$A2 is the product name that we want to match in the first column of the original table.
A1:Z100 is the range of the original table where we want to look for the value.
0 in the MATCH function specifies that we want to find an exact match.
B$1 is the provider name that we want to match in the first row of the original table.
+1 in the second MATCH function is used to offset the column number by 1, because we want to skip the provider name and retrieve the score value instead.
However, now thinking about it, ChatGPT should help you with this! Explain your table and how you need to extract data (you can even create tables like Product A | Provider 1 | Score | Product B | Provider 2 , etc.) and, from my experience, ChatGPT works great with Excel.
Thank you so so so much Cjortuzar! I'll check with ChatGPT too!
Were you able to get it done? If not, I have some free time now and would gladly help :)
Hey Cjortuzar! So sorry I missed this, I was away for the weekend!
I'm trying to work it into my bigger dataset right now. It works for the one cell, but not when I drag the formula across? :(
Yes, noticed that. It has to do with how we're fixing the values ($) so they don't move when copying across. I've recreated the whole table below in your Google Sheets file with a formula that should be correct and let you drag across.
If it doesn't work out, let me know :) We can even schedule a zoom call.
Wow, you are a (kind) genius.
Thank you ever so so so much! I will test it out on my larger dataset and get back to you if I get into any errors.
I can't express how thankful I am for that!