Index and Match problems.

[trekker1218]

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.

[TABLE="width: 1722"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Attribute Label 1[/TD]
[TD]Attribute Value 1[/TD]
[TD]Attribute Label 2[/TD]
[TD]Attribute Value 2[/TD]
[TD]Attribute Label 3[/TD]
[TD]Attribute Value 3[/TD]
[TD]Attribute Label 4[/TD]
[TD]Attribute Value 4[/TD]
[TD]Attribute Label 5[/TD]
[TD]Attribute Value 5[/TD]
[TD]Attribute Label 6[/TD]
[TD]Attribute Value 6[/TD]
[/TR]
[TR]
[TD]Brand Name[/TD]
[TD]Gastite[/TD]
[TD]Sub Brand[/TD]
[TD]FlashShield[/TD]
[TD]Item Name[/TD]
[TD]Corrugated Tube[/TD]
[TD]Type[/TD]
[TD]Metallically Shielded, Flexible[/TD]
[TD]Nominal Size[/TD]
[TD]1/2 Inch[/TD]
[TD]Size[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[MartyS]

=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.

 

[trekker1218]

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.



[TABLE="width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[/TR]
[TR]
[TD]Attrib1[/TD]
[TD]Value1[/TD]
[TD]Attrib2[/TD]
[TD]Value2[/TD]
[TD]Attrib3[/TD]
[TD]Value3[/TD]
[TD]Attrib4[/TD]
[TD]Value4[/TD]
[TD]Attrib5[/TD]
[TD]Value5
[/TD]
[/TR]
[TR]
[TD]Size[/TD]
[TD]small[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Size[/TD]
[TD]Large[/TD]
[TD]Color[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD]Color[/TD]
[TD]black[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SIze[/TD]
[TD]medium[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Color[/TD]
[TD]reb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Color[/TD]
[TD]Green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

=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.

 

[MartyS]

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.

 

[trekker1218]

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.

 

[trekker1218]

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

 

[MartyS]

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...

 

[MartyS]

Added an extra sheet so it matches the layout you originally had.

https://www.dropbox.com/s/tpctauuplyl8jij/AmendedMessyData2.xlsm?dl=0

 

[trekker1218]

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...

 

[trekker1218]

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