VLOOKUP & IF Function

[agroeneveld]

Attached is a sample of what I need.

I have a complete list of students...I have multiple sheets that have lists of the kids in each of these classes...

On the Combined sheet, I need There to be an X if the kids are in those classes.


THanks!

[AliGW]

In B2 on the combined sheet, copied across and down:

=IFERROR(IF(COUNTIF(INDIRECT("'" & B$1 & "'!A:A"),Combined!$A2)=0,"","x"),"")

[JohnTopley]

In B2

=IFERROR(IF(MATCH($A2,INDIRECT("'" &B$1 &"'!$A$1:$A$100"),0),"x",""),"")

Copy across and down

[agroeneveld]

I am unsure of how the other sheets are factored into the formula. Can someone please explain this? or show me on the spreadsheet?

Thank you

[AliGW]

Of course. This bit:

INDIRECT("'" & B$1 & "'!A:A")

is looking at the column header on the combined sheet to decide which sheet to look at, using the name in the column header as the sheet name. As you drag across, B$1 increments to C$1 ans so on. The indirect function allows you to use a reference in a cell as a sheet name in this way.

[Andrew Crystal]

Very similar to the above you could also use:

=IFERROR(IF(MATCH($A3,Gym!A:A,0),"X"),"")

You would have to change the sheet in reference in the middle for each column though so this one would go into column B

=IFERROR(IF(MATCH($A3,Math!A:A,0),"X"),"") - column C

=IFERROR(IF(MATCH($A3,Science!A:A,0),"X"),"") - column D

Probably best to use one of the other though as the INDIRECT reference sorts that for you

Also you might be wise to check for duplicates in your combined list as copying from each sheet has caused some duplication I think

[agroeneveld]

Ok. Awesome. One thing...is there a way to make this work where the names do not have to be 100% the same...

It's refusing to pull data if it is missing even a comma.

Thank you

[AliGW]

Which names? The pupil names? Essentially, yes: if you are matching one thing to another, it must be the same. Why wouldn't it be, anyway? It isn't usual to use names as unique identifiers, anyway: by definition, they aren't! What about using the ID number from your school database?

[Andrew Crystal]

Like Ali said it can be done but why would you want to? His suggestion to use something else as the identifier is also sound - if you think you only have a few people in the file at the moment and you already have duplicates and you are going to have problems when the school has 2 people called Joe, 10 people called Sally etc. You could possibly do it by using their last names as well (though this isn't guaranteed as more than one person might have the same firstname and lastname) but you really need to start using a unique identifier now to avoid future problems.

[agroeneveld]

The school spreadsheet was just an example. What I am actually working with are hundreds of company Names and spreadsheets that different people have created....so some of the spreadsheets have Company names in , or - or . while others do not.

The amount of time it would take to go through each one by one would be too long.

If there is a way to do this, you are all amazing.

Thanks... *fingers crossed*

[Andrew Crystal]

The problem isn't so much whether it can be done or not it's more whether the results will actually mean anything.

For example if we have 3 companies called smith & son, smith & sons and smith and son ltd and you change the search so that it can allow anything containing smith then these results would all be counted 3 times so I think it would be quite difficult to be confident of the accuracy of results.

How flexible would you want it to and is it just whether it contains commas or other punctuation, case sensitive etc?

[agroeneveld]

Basically the only issue I see is with commas and periods....everything else is ok.

[Andrew Crystal]

OK so sometimes a company name might have a comma or period in it and sometimes it won't but aside from that it will all match?

And also do they need to be there?

[AliGW]

Like Ali said it can be done but why would you want to? His suggestion ...

Just for the record, Andrew, he is a she. Maybe you can't see my avatar?

[AliGW]

You probably need to do some sort of data sanitisation prior to using these lists collated from all over the place, but it would be much easier to assign each company an ID number.

When you get the data, is it at least consistent from each source? For example, does source A always refer to Smith & Co. where source B refers to Smith and Co? If so, you could create a lookup table with the variations and from that assign a code. Or you could go down the substitution of commas and full stops (periods) for blanks. It really depends on the nature of the data and how you want to process it.

[Andrew Crystal]

Oops sorry Ali, hadn't really noticed the Avatar but no offence intended - unfortunate lack of an embarrassed smiley too it would appear.....

[agroeneveld]

The periods and commas don't even need to be there. I wasn't sure if Excel had a way to delete them

[AliGW]

Yes, it can, but we'd have to see a sample of the real layout of your data (desensitised) to help advise how best to do it. Essentially, you could do this:

=SUBSTITUTE(SUBSTITUTE(A1,".",""),",","")

where A1 contains text with either commas or full stops.

[agroeneveld]

If I try to sort my data, the X's will not sort properly....is there a way to fix this?

[agroeneveld]

Hello, Thanks for all of your help. I have a few more additions to this formula, if you would be able....

1. I need to formula adjusted so that an X appears in the combined sheet under the appropriate name ONLY if there is an amount in Column C of the other sheets (gym, math, science)

2. I need to be able to sort the data by partner name or state if need be....and have all of the x's move accordingly.

Thank you so much!

[AliGW]

Please provide an updated workbook with the formulae as you currently have them in place and a few expected values.

You will not be able to sort rows with formulae in them, so you are going to have to rethink that.