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]

I'll have a look at it this evening and over the weekend.. and get back to you asap..!

 

[MartyS]

Try it now.


https://www.dropbox.com/s/6o45n9hqtyj6wek/AmendedMessyData_XX.xlsm?dl=0



There's an instruction sheet which will guide you through the process.


Basically, paste your data into the "DirtyData" worksheet.

If there's already data IN that sheet, click on the DELETE SOURCE DATA button and it'll remove it all. Only use this when you want your source data deleted though (have the original data stored somewhere).


Once the data is in this sheet, starting in the right cell (it'll be obvious, and the instructions state which cell also)....

Click on the CREATE UNIQUE ID button.


Then each button in sequence (the instructions tell you what to do) EXCLUDING the [DELETE ALL SOURCE DATA] button, of course.


You should be able to use pretty much any reasonably sized data.




The format you want is in the SECOND table (not the first).

You can create the second table immediately after the first has been created.




Let me know how you get on.

 

[trekker1218]

"WOWWWW" Sorry for the delayed response. Was traveling and could not access these files.
Incredible solution. You all ROCK>>>

THANK YOU>
Tino

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

 

[MartyS]

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

Hi Tino

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

1. Could you please take a look at this - and you will find the isn't a problem.

If you look at your original data you will see the routine has simply copied what is there.

The REFERENCE SHEET is "exactly what it says on the tin", as they say.

ALL it is.. is your original source data split (two columns kept together at a time) and pasted one below the other...

e.g.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Label A1[/TD]
[TD]CodeB1[/TD]
[TD]Label C1[/TD]
[TD]CodeD1[/TD]
[TD]Label E1[/TD]
[TD]CodeF1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Label A2[/TD]
[TD]CodeB2[/TD]
[TD]Label C2[/TD]
[TD]CodeD2[/TD]
[TD]Label E2[/TD]
[TD]CodeF2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Label A3[/TD]
[TD]CodeB3[/TD]
[TD]Label C3[/TD]
[TD]CodeD3[/TD]
[TD]Label E3[/TD]
[TD]CodeF3[/TD]
[/TR]
</tbody>[/TABLE]


Copied so that it looks like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Label A1[/TD]
[TD]CodeB1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Label A2[/TD]
[TD]CodeB2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Label A3[/TD]
[TD]CodeB3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Label C1[/TD]
[TD]CodeD1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Label C2[/TD]
[TD]CodeD2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Label C3[/TD]
[TD]CodeD3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Label E1[/TD]
[TD]CodeF1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Label E2[/TD]
[TD]CodeF2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Label E3[/TD]
[TD]CodeF3[/TD]
[/TR]
</tbody>[/TABLE]

And many more below that.

Now you can see this, you'll also be able to see that whatever is below the cell you mentioned is EXACTLY the same as what's in your table.

No "processing" has been done on it at this stage... it's literally JUST the data in your table split up into it's component parts, and copied DOWNWARDS!

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.

Now you understand this, you'll get that there is no need (on this sheet, at least) to "copy down formulas" because there aren't any...

It's simply your original data ...structured differently.

Again... NO processing / formulae / etc has been done on it at that stage.






OK - I've looked at the spreadsheet you returned, and it's not the LASTEST version of the sheet I gave you.


If you look back through these posts, you'll see a LINK to a spreadsheet that does ALL that you need it to.

That post also describes that the data output you want is in the SECOND output table.

AND: You say you have many tables (I think you said 300) - you can paste data of any number of columns (can go far to the right >>>) and any number of rows (can go far down VVV ) and still process it.

Of course, there is a limitation, but I doubt you'll meet it (you'd need thousands of rows in your source data and many columns before Excel reaches it's limit of MAX ROWS).


Hope that helps clear things up?

 

[MartyS]

Oops.. sorry, ignore my last post - hadn't realised you'd added more replies (didn't see page 2) ha ha!!

"WOWWWW" Sorry for the delayed response. Was traveling and could not access these files.
Incredible solution. You all ROCK>>>

THANK YOU>
Tino

Glad it worked!

 

[trekker1218]

Hello, again,

This file has been a life saver. I have run into a runtime error when i try to sort more than a 1000 rows of data.

I basically narrowed it down by trying 5000 then 4000 and so on. It works all the way through when you try 1000 or less.

can this be altered to work with 5000 items.

please advise...
thanks,