Index and Match problems.

trekker1218

Board Regular
Joined
Feb 15, 2018
Messages
86
Office Version
  1. 2019
Platform
  1. Windows
I am trying to line up columns of data that are all jumbled. Example. length x width x height are in my sheet in a row. But are in different columns. I've tried to Index and match to no avail. How can I search for a cell label marked WIDTH in a row and then get the next cell to the right with the WIDTH VALUE. I am using 2 sheets. one with all the data and a blank. I though I could Index the row and match the data cell.

=INDEX(Sheet2!BG3:FH3,MATCH(BG3,Sheet2!BG3:FH3,FALSE),2)
row of data row of data

this works for my first column only. When i apply it to other columns. its give me an error or no values when I know the values are there.

Attribute Label 1Attribute Value 1Attribute Label 2Attribute Value 2Attribute Label 3Attribute Value 3Attribute Label 4Attribute Value 4Attribute Label 5Attribute Value 5Attribute Label 6Attribute Value 6
Brand NameGastiteSub BrandFlashShieldItem NameCorrugated TubeTypeMetallically Shielded, FlexibleNominal Size1/2 InchSize#N/A

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
=INDEX(Sheet2!BG3:FH3,MATCH(BG3,Sheet2!BG3:FH3,FALSE),2)


Look at the highlighted part of your INDEX statement.

This first part needs to reference the entire table you want to lookup.

Currently you're only referencing the first row.

Say it has 10 rows of data.... and row 1 of data is BG3 - it'll need to be Sheet2!BG3:FH13



With regards to your data being "jumbled" - not sure quite what you mean by this, but it could be that if pasted (or text-to-columns was used) there were incorrect delimiters and so data would skip cells..

e.g.

cat, 10, , black
dog, 5, 1.2, grey
mouse, 1, brown

there's a comma missing in the third line of data. When pasted into cells/text-to-columns and the comma is used as the delimiter, brown (for the mouse colour) will end up in the third column rather than the fourth.
 
Upvote 0
Marty, Thank you for responding. Here is a quick sample. The data is in the middle of the sheet and I can't shift empty spaces. I am using a blank sheet to reference this data with the Index and Match. How would you suggest getting all the SIZE - COLOR to line up in one column without losing the row integrity. I was using a blank sheet to reference this data.



abcdefghij
Attrib1Value1Attrib2Value2Attrib3Value3Attrib4Value4Attrib5Value5
Sizesmall
SizeLargeColorBlueColorblack
SIzemedium
Colorreb
ColorGreen

<tbody>
</tbody>


=INDEX(Sheet2!BG3:FH3,MATCH(BG3,Sheet2!BG3:FH3,FALSE),2)


Look at the highlighted part of your INDEX statement.

This first part needs to reference the entire table you want to lookup.

Currently you're only referencing the first row.

Say it has 10 rows of data.... and row 1 of data is BG3 - it'll need to be Sheet2!BG3:FH13



With regards to your data being "jumbled" - not sure quite what you mean by this, but it could be that if pasted (or text-to-columns was used) there were incorrect delimiters and so data would skip cells..

e.g.

cat, 10, , black
dog, 5, 1.2, grey
mouse, 1, brown

there's a comma missing in the third line of data. When pasted into cells/text-to-columns and the comma is used as the delimiter, brown (for the mouse colour) will end up in the third column rather than the fourth.
 
Upvote 0
Marty, Thank you for responding. Here is a quick sample. The data is in the middle of the sheet and I can't shift empty spaces. I am using a blank sheet to reference this data with the Index and Match. How would you suggest getting all the SIZE - COLOR to line up in one column without losing the row integrity. I was using a blank sheet to reference this data.


Is there any chance I could have a look at the spreadsheet itself (do you have DROPBOX or some other file-sharing tool) - assuming it's not confidential information, that is.

Sorry - it's a bit difficult to see what's going on with just the information given.
 
Upvote 0
Hi,
The data is for my company web page. I need to upload the data into a content manager in a particular format (Sheet2)
The column sort order does not matter. But the Rows must remain in the sequence they are.

heres is a dropbox link
https://www.dropbox.com/s/lpixj2jnltyy0zv/attribute sample.xlsx?dl=0

Sheet 1 is the raw data.
Sheet 2 is what i need it to look like when done.

Each row is unique with a model number.



Is there any chance I could have a look at the spreadsheet itself (do you have DROPBOX or some other file-sharing tool) - assuming it's not confidential information, that is.

Sorry - it's a bit difficult to see what's going on with just the information given.
 
Upvote 0
Hello,
I only manually sorted up to column AK as you can see. I do need to sort ALL columns. The Attribute Labels need to match the Values in next cell to the right.
I was using a blank sheet with formula that reads each row of Sheet1 to find the MATCHING label I Designate and then place the VALUE next to it. Then repeat the process for all unique Labels.
Each row is unique. So if I search for label SIZE in a row. I will only find 1.

Hope this helps...

And a very big thank you for all your help.
tino
 
Upvote 0
Ok.. now I understand what you mean by jumbled data!

https://www.dropbox.com/s/08zbiajgkg2ptk3/AmendedMessyData.xlsm?dl=0


To get the data into the format you wanted, I had to alter the data structure...

There are two buttons on the main sheet.

Click the first and it will create three columns of data that represent your existing table.

Click the second button and it simply copies that data into the second sheet where a pre-existing (empty) table has formulas in it.. and that gets auto-populated after the copy.

The third sheet is not really needed, but in case you just wanted the output data in the desired format.. there it is.

You may need to tidy it up just a tiny a bit (i.e. there are columns called [0] and [ ] and there are columns with zeros for data where the Attribute was supplied (i.e. color) but the associated value wasn't).

Let me know if you have any problems...
 
Upvote 0
Hello,
That is workable. Thank you. However, what do I need to change if the ROWS are increased from the sample file. The sample had 35 rows. I have over 300 files to do with various amounts of rows.

thanks again.



Ok.. now I understand what you mean by jumbled data!

https://www.dropbox.com/s/08zbiajgkg2ptk3/AmendedMessyData.xlsm?dl=0


To get the data into the format you wanted, I had to alter the data structure...

There are two buttons on the main sheet.

Click the first and it will create three columns of data that represent your existing table.

Click the second button and it simply copies that data into the second sheet where a pre-existing (empty) table has formulas in it.. and that gets auto-populated after the copy.

The third sheet is not really needed, but in case you just wanted the output data in the desired format.. there it is.

You may need to tidy it up just a tiny a bit (i.e. there are columns called [0] and [ ] and there are columns with zeros for data where the Attribute was supplied (i.e. color) but the associated value wasn't).

Let me know if you have any problems...
 
Upvote 0
Hello,
I am enclosing a secondary sheet with more rows for you to review. I notices some data is still incorrect with your formulas.
https://www.dropbox.com/s/2k869wjzuhv0tzl/AmendedMessyData-2.xlsm?dl=0

if you look at your (referenceTheData) sheet. Scroll down on column B row 897 you will see an incorrect entry. And many more below that.

If there a way to modify what you have done to fill one row and allow me to FILL DOWN your formulas to accommodate the qty of rows per sheet. The amount of Attributes vary as well. Some sheets will have a few and max out at 50 total.

I had to modify the ref sheet to insert columns to line up the Attributes and Labels.

This is an amazing help and greatly appreciated.

thanks again,
Tino
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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
Back
Top