Index Match Functions - Duplication of Text

Why don't you use the No column (A) as the lookup value? Those seem to be unique, and you could use VLOOKUP rather than INDEX/MATCH.

Hope this helps.

Pete

Originally Posted by Pete_UK

Why don't you use the No column (A) as the lookup value? Those seem to be unique, and you could use VLOOKUP rather than INDEX/MATCH.

Hope this helps.

Pete

My apologies, that column shouldn't have been used. I prepared a sample table of the file I'm using. The actual file itself isn't numbered like that, hence why I have to use Index/Match Function rather than a simple vlookup.

Also, I have multiple categories so for example, the table has able 20 - 30 sections, rather than just that one table at the beginning. The source information also does not have a"No." column.

My apologies again I should have excluded that from sample file. If there were numbered life would be so much easier! But unfortunately not, hence the reason for the Index/Match function.

You said in the first post that you wanted to filter some information to the other sheet, although the file that you attached (subsequently amended) just shows a straight copy of the data.

How would you envisage that the filtering would work? Would you have a drop-down in a cell in the TP1 sheet which would allow you to choose from a list? Which column would you like this to act upon - the name, or the status, or something else?

Pete

Originally Posted by Pete_UK

You said in the first post that you wanted to filter some information to the other sheet, although the file that you attached (subsequently amended) just shows a straight copy of the data.

How would you envisage that the filtering would work? Would you have a drop-down in a cell in the TP1 sheet which would allow you to choose from a list? Which column would you like this to act upon - the name, or the status, or something else?

Pete

Hi Pete,
Let me further explain.

The structure of the file that I am currently working with is a bit more extensive. I only provided a sample of what it actually looks like. I changed the file “No.” because technically the real information is not like that, i.e the variables aren’t really numbered like that. Hence the reason I updated the excel file.

Essentially TP1 has that same structure, but like 20 – 30 sections/categories. SI is the raw source data, which varies significantly, for example, today the file may have 20 variables tomorrow it could 150 variables. Also there are no unique variables that I can use to do a simple vlookup, hence why I needed to use the Index/Match function.

What I have done is preformat the TP1 table, as I know that there are some instances (not all) where certain variables will always be captured under a particular category. For example, if one category is for Vegetables, then only Tomatoes, Cucumbers, Sweet Peppers etc. can be in that section. So…I already have the variable titles prelisted under its respective category in TP1.

The problem I have is that the source data may have, using an example again, Cucumbers listed twice but the price, the weight or some other category may vary. Therefore what I need is that if I have cucumbers in my preformatted list twice that it doesn’t duplicate the first instance from the source data.

Also there are instances were items may be abbreviated or have some other variance. So my preformatted table can capture some items but not everything. There will definitely still be some form of manual intervention throughout the process. I am just trying to automate the process where it’s most possible.

I was trying to keep my example as simple as possible not to lose anyone, I know it is a tad bit confusing when you’re not seeing the full picture.

Thanks I'll try this out and see how it works with what I am working on!

Originally Posted by JeteMc

1) Paste the following array entered formula in TP1!C3:

Formula:

Please Login or Register  to view this content.

2) Press the F2 key,
3) Simultaneously press Ctrl, Shift and Enter,
4) Copy down and across
Let us know if you have any questions.

Thanks I'll try this out and see how it works with what I am working on!

Originally Posted by JeteMc

1) Paste the following array entered formula in TP1!C3:

Formula:

Please Login or Register  to view this content.

2) Press the F2 key,
3) Simultaneously press Ctrl, Shift and Enter,
4) Copy down and across
Let us know if you have any questions.

Hi JeteMac,

So I was able to use this in the sample I provided, but I'm having issues using it for a larger data set. What if the data in the SI tab had like over 80 variables? I tried to make adjustments to the formula, but I'm not having much luck.

Originally Posted by JeteMc

When you say over 80 variables I am assuming that you mean there could be more than 80 names in column A of the SI sheet. I have updated the array entered formula to look at 200 names:

Formula:

Please Login or Register  to view this content.

Remember to activate the array entered formula as described in post #4, because simply pressing the enter key will result in incorrect results.
Let us know if you have any questions.

Thanks this worked! I probably made an error somewhere. Thanks for the assistance!