Is anyone out there an expert with Excel?

[bits]

I need help with one thing and I can't figure out how to write a macro to do it automatically.

[silk]

Macros are beyond me but I can possibly help.

[Dreamer]

You can record a macro that copies steps you do manually. I'm not sure I understand what you are trying to do though. I'm proficient at this in word and not as good in excel but they are pretty similar. You might need to turn on the developer ribbon to get started if you don't see it. Once you have the developer ribbon showing you can record a macro and then use it later to " automate" steps.

[bits]

Maybe a macro isn't what I need... I can do macros but when I try it with this, everything I want moved into a different cell copies to the exact same cell each time.

Here's the situation... It may be hard to visualize..lol but I'll try to write it out.
I have several different worksheets each with some of the same line 'topic' but each topic has a number associated with it. Each worksheet is different but has the same 'topics' in each line, just the number is different. I want to have a master list that combines each worksheet in a way that a separate column holds each number for the 'topic' in one single line. But I need to this to be done automatically as there are over 40,000 lines.

so example...

worksheet a:
taco tuesday 598
glass half full 61
empty container 36
lazy susan 1888

worksheet b:
empty container 877
lazy susan 941
taco tuesday 866
glass half full 799

worksheet c:
lazy susan 2500
taco tuesday 1782
glass half full 1900
empty container 645

I want to end up with a master sheet that looks like this and does it automatically:

lazy susan 1888 941 2500
glass half full 61 799 1900
empty container 36 877 645
taco Tuesday 598 866 1782

[silk]

vlookup, though tedious and resource hungry.
index and match is a better solution.

[silk]

you may need indirect as well as index/match, if you only want to look at part of a cell (eg the text, not the number). Else split text to columns first, so the number is in a separate column from the text.

[silk]

also, make your array (the information you're looking in) a table. much easier for formula creation.

[bits]

I have tried making an array with index and match but I can't get it to work.

[silk]

I'll see if I can send you a hint tomorrow when I'm looking at my insane spreadsheet, created by an Excel Geek.

[Maggie]

Are "empty container", "lazy susan", 877, and 941 each in their own cells (so those would be 4 different cells on worksheet b)? Or is "empty container 877" the contents of a single cell? Or, if neither of those 2 are correct, can you explain how the data goes into the cells?

[silk]

Index and Match

Index has three arguments.
Array (this is the thing that you want to return), Row_num, and Column_Num.

You use Match as the second argument, Row_Num.


Firstly... =Index([select the result you want to find/return, eg 456],Match(...


Now, you have to work out what to match.
Match also has three arguments. Lookup_value, Lookup_array and Match_Type.
Lookup_value is the thing you want to match to, in the table you're building the formula in (the cell that contains 'empty container')
Lookup_array is the answer you want to find/return, from the place the data is stored (the other sheet - eg the number attached to 'empty container, which would be 456)

so... Match([select the result you want to match to eg 'empty container'],[select the cell that the answer you want is in eg 456])

Then close off the Match, comma, 0 for exact match, finish...

Should work, if I've typed it out right. I just built one for myself and made it work

It will look like this
=Index([select the result you want to find/return, eg 456],Match([select the thing you want to match to eg 'empty container'],[select the cell that the answer you want is in eg 456]),0).

You will have to do this for each of the sheets, in three columns, to end up with "empty container 456, 789, 123".

I've done it briefly in one sheet rather than three, so you can see how it works...

Capture.PNG (22.68 KiB) Viewed 12232 times

[bits]

That's what I tried... but I'll do it again.

[Maggie]

You might want to try the following to expand on silk's, this would go in cell F2 (after you type in silk's sample Sheet1 data into an actual tab called Sheet1:

=INDEX(Sheet1!A1:B5, MATCH($E2,Sheet1!A1:A5,0),2)

Starting with the MATCH function:

E2 is where "empty container" is in the example and what your MATCH function is trying to search for. The rectangle of cells from A1 to A5 is the column in which "empty container" can be found on Sheet1. The 0 after that is just to indicate you want an exact match.

A dollar sign is put before the E in $E2 to indicate you want the E part of the formula to stay the same as you drag the formula from cell F2 to G2. You still want to use "empty container" across that row for your lookup. I'd also put dollar signs so your references don't get messed up on Sheet1 (ie. Sheet1!$A$1:$A$5).

Sheet1!A1:B5 you can see is referring to the rectangle of cells from A1:B5 - this is considered an array. Using the row number within the array/rectangle from the MATCH function, and the fixed number 2 for the column part, the INDEX function can find the value for "empty container" in the 2nd column of the array. In this example (assuming Sheet1 was a separate sheet so my formula would be correct), MATCH should return 2 for the row piece, 2 is the column piece, and so within A1 to B5 you'd go to the 2nd column and 2nd row to cell B2. 456 would be returned.

Hopefully that gives you something to jump off from. I'm happy to help more if that doesn't make sense for you.

[bits]

I'm using array's instead of individual cells since I don't know what line item the number or match will appear on. and yep, I'm using all of that but it won't pull for some reason.

[bits]

oops... I just figured it out... I had the index, array, match, etc.. and then 0 for exact match but I forgot to remove the column number at the end. I originally had a huge array instead of individual columns as arrays. I think I got it...

[silk]

The point of index/match is it will find things in a row, or column, or both. You just can't have two identical lookup entries with different results - eg two lots of Lazy Susan in the list - but you can apparently match with two criteria (I don't know how!). My example only has cell refs as it's little... The best way is to use a table so you can reference the table column, rather than the column in excel. Tables are great for helping you write tidy and understandable formulae.