Excel help please - any wizards on NSC?!

[Lindfield by the Pond]

Don't know much about spreadsheets but bounced it as keen to see answer

 

[portlock seagull]

Don't know much about spreadsheets but bounced it as keen to see answer

Thanks! I think I'll put out to tender on Upworld.com tomorrow first thing. It's something could have done with years ago really, in house IT teams used to assist with 'fuzzy logic' but no one really managed to solve so thought I'd ask a wider community for help. NSC has solved a lot more bizarre matters than this request down the years so thought I'd give it a go!

 

[Buzzer]

Thanks! I think I'll put out to tender on Upworld.com tomorrow first thing. It's something could have done with years ago really, in house IT teams used to assist with 'fuzzy logic' but no one really managed to solve so thought I'd ask a wider community for help. NSC has solved a lot more bizarre matters than this request down the years so thought I'd give it a go!

I'll try to look at this tomorrow for you but I've just got in so gonna turn in right now,

 

[father_and_son]

You need to change the formula to take into account that you are using more than 4 columns for the names (which was all the original formula was written for)...

So for each column after E you need to repeat the "search" part.




START WITH : =INDEX('Sheet2'!A:A,MAX(

THEN YOU NEED: IFERROR(MATCH('Sheet1'!G2,'Sheet2'!B:B,0),0), ---- For each column that there are names in ---- changing the B:B to C:C then D and keep going until you have Z:Z (or whenever you run out of columns with names in)

DON'T FORGET TO PUT A COMMA AFTER EACH OF THE "IFERROR" TERMS, EXCEPT DON'T PUT A COMMA AFTER THE LAST ONE

FINALLY: CLOSE THE BRACKETS FOR THE MAX STATEMENT AND THE INDEX STATEMENT .... SO END WITH ))




There is a limit to how many characters can be in a single formula and so if there are too many columns then this won't work and you will have to do it in visual basic.


Also, if a name is repeated anywhere then the formula will behave a little bit strange. So if right down that the bottom of column A were some information that wasn't part of the table but met one of the match criteria then you'd get that response instead of, say, the actual match in column B.



Might be worth sorting the whole list of companies by Company ID so that you can see quickly if there are any duplicate numbers.

 

[Buzzer]

I'm also on the case with this but I've had a few gin and tonics at lunchtime so I probably won't get to have a look at this until tomorrow now although I do like the solution offered above.