Excel help please - any wizards on NSC?!

[portlock seagull]

I'd be eternally grateful if anyone wants to assist, it's to do with finding name variations within a table array and if found, returns a value from another column within that table array.

Table Array has these variations on a name by column e.g.
(A = 92pts)
B =Brighton
C=Brighton&HA
D=Brighton&HoveAlbion
E=BrightonHoveAlbion
F=BrightonAndHoveAlbion

I'm looking to find Brighton&HoveAlbion (for sake of the following, let's call that A1), so using 'SumProduct(--Isnumber(search(A1,B:F)))>0' it returns 'True' because 'Brighton&HoveAlbion' is indeed one of the spelling variations in column D. So far so good.

However, having found 'A1' within this table array, I also want it to return the value that's in col.A e.g. 92 pts - anyone know how?! Basically I'm searching for variations on a company name within a massive table array and if found, I want to also return the associated value.

 

[AmexRuislip]

Excelliarmus

 

[Buzzer]

Must confess, I consider myself a bit of an Excel wiz but you've lost me here with what you're trying to do. Can you post a screenshot of a sample of the spreadsheet?

 

[coagulantwolf]

I'm also slightly confused by your explanation but potentially VLoookUp?

 

[KingKev]

Must confess, I consider myself a bit of an Excel wiz but you've lost me here with what you're trying to do. Can you post a screenshot of a sample of the spreadsheet?

Same here - no comprendo - but you may be looking for a vlookup?

 

[Mattywerewolf]

Think you need to say

If(Isnumber.......,lookup(.........),"bolx")

basically if the 'is number' returns a true, then do a vlookup on the name you want, otherwise the cell will say "BOLX"

 

[portlock seagull]

Hi, here's a screenshot to explain better (sorry folks!). So you can see what I'm searching for: the names in column G.

Having found them - because they're are multiple ways of spelling these 2 teams, shown in columns B to E - in the adjacent 'table array' (using the formula shown in the function bar), I want to return the value shown in Column A i.e. 92pts.

In other words, I'm searching for a company name, which could be spelt in multiple ways, within an array of different spellings, and if found I want it to then return the value in col.A i.e. 92pts in this instance.

Make sense? Thanks indeed for the replies so far and if someone can crack it, I'm indebted! Tried loads of excel forums but frankly they're even more complicated trying to understand!

 

[martin tyler]

I'd be eternally grateful if anyone wants to assist, it's to do with finding name variations within a table array and if found, returns a value from another column within that table array.

Table Array has these variations on a name by column e.g.
(A = 92pts)
B =Brighton
C=Brighton&HA
D=Brighton&HoveAlbion
E=BrightonHoveAlbion
F=BrightonAndHoveAlbion

I'm looking to find Brighton&HoveAlbion (for sake of the following, let's call that A1), so using 'SumProduct(--Isnumber(search(A1,B:F)))>0' it returns 'True' because 'Brighton&HoveAlbion' is indeed one of the spelling variations in column D. So far so good.

However, having found 'A1' within this table array, I also want it to return the value that's in col.A e.g. 92 pts - anyone know how?! Basically I'm searching for variations on a company name within a massive table array and if found, I want to also return the associated value.

I think I have now got what your talking about. I could prob send you what you need once indoors if that helps

 

[portlock seagull]

I think I have now got what your talking about. I could prob send you what you need once indoors if that helps

A beer coming your way at the Wigan game if so!! I'm definitely stuck - and I'm the Excel 'guru' in my business!

 

[portlock seagull]

Same here - no comprendo - but you may be looking for a vlookup?

Thanks King Kev, it's a bit more tricky than a standard vlookup because I'm searching to find a match across several columns first, rather than just one static column; and then I want it to return the associated value (92pts) if it does find a match to one of the spellings.

But for your interest I shall tell you that 'King' in Danish is 'Kong' so you're 'Kong Kev' in Denmark! Always amused me that 'King Kong' in Danish is therefore 'Kong Kong'!
Tac!

 

[North East Seagull]

If am not, by any means, an excel expert but use it a fair bit. Sounds like you have two operations you want to do; identify name variations and return some associated data to a new column. Ideally I would use the 'like' function in a logical statement. Something like =IF(A1 like 'Brighton',cell with number in,"") Thing is I am not sure of the exact syntax of using like function in the logical statement.
What should work (and I would probably do) is: Add in couple of columns, put a filter on column headings, filter company column for 'contains "Brighton" . Stick a flag (x) in one of the new columns, sort by the flag column and then do a logical statement in second new column: =IF(flag cell <>0,"",cell with data needed in) Use the quick way to copy formula down and or infill other cells. Not very slick but should work.
Be interested to hear how you crack this one.

 

[portlock seagull]

Must confess, I consider myself a bit of an Excel wiz but you've lost me here with what you're trying to do. Can you post a screenshot of a sample of the spreadsheet?

See below and thanks for trying to help. Yes, it was a bit of a mess trying to explain without a screenshot. Now provided.

 

[TimWatt]

Isn't what you're trying to do what is known as a 'regular expression' - i.e. a way to automate the tidying of lots of text, and therefore doesn't really require an Excel function at all?

This is merely a text function so I'd assume you can export as CSV and perform a suitable regular expression doing multiple 'find and replaces' and then reimport the CSV as tidied up text. That can then all be automated so can be repeated at the touch of a button.

On a Mac you could write an Applescript or, simpler, compile an Automator workflow - other scripting languages are available...

If the source data comes form elsewhere perhaps you could cut out a stage by performing the script on the CSV before importing to Excel. Or importing to a database rather than Excel at all might be streamline that further...

 

[portlock seagull]

Isn't what you're trying to do what is known as a 'regular expression' - i.e. a way to automate the tidying of lots of text, and therefore doesn't really require an Excel function at all?

This is merely a text function so I'd assume you can export as CSV and perform a suitable regular expression doing multiple 'find and replaces' and then reimport the CSV as tidied up text. That can then all be automated so can be repeated at the touch of a button.

On a Mac you could write an Applescript or, simpler, compile an Automator workflow - other scripting languages are available...

Probably, but I've not got those skills and the industry I'm working with is extremely s/sheet based so it's the currency I must use so's to speak.

 

[TimWatt]

Your call, but doing some English editing using some scripting seems much easier to me than editing text using maths! ... and can be automated...

 

[Buzzer]

See below and thanks for trying to help. Yes, it was a bit of a mess trying to explain without a screenshot. Now provided.

I think you're almost there if I've understood correctly. You've got the TRUE or FALSE bit worked out and if you just stick your formula to obtain TRUE into

=IF(TRUE,INDIRECT("A"&ROW()),"")

then I think you're done, as follows: I've got a value in C3 and a formula in D3 that checks if C3 is TRUE and if so goes and gets the value in column A of the same row otherwise it returns a blank value ("").

So your formula in your column H would be =IF(SumProduct(--Isnumber(search(A1,B:F)))>0,INDIRECT("A"&ROW()),"")

 

[portlock seagull]

If am not, by any means, an excel expert but use it a fair bit. Sounds like you have two operations you want to do; identify name variations and return some associated data to a new column. Ideally I would use the 'like' function in a logical statement. Something like =IF(A1 like 'Brighton',cell with number in,"") Thing is I am not sure of the exact syntax of using like function in the logical statement.
What should work (and I would probably do) is: Add in couple of columns, put a filter on column headings, filter company column for 'contains "Brighton" . Stick a flag (x) in one of the new columns, sort by the flag column and then do a logical statement in second new column: =IF(flag cell <>0,"",cell with data needed in) Use the quick way to copy formula down and or infill other cells. Not very slick but should work.
Be interested to hear how you crack this one.

Giving it a go now. Thanks. Will let you know accordingly

 

[portlock seagull]

I think you're almost there if I've understood correctly. You've got the TRUE or FALSE bit worked out and if you just stick your formula to obtain TRUE into

=IF(TRUE,INDIRECT("A"&ROW()),"")

then I think you're done, as follows: I've got a value in C3 and a formula in D3 that checks if C3 is TRUE and if so goes and gets the value in column A of the same row otherwise it returns a blank value ("").

So your formula in your column H would be =IF(SumProduct(--Isnumber(search(A1,B:F)))>0,INDIRECT("A"&ROW()),"")

Ah, sorry, the table array is in another worksheet so that won't work.

 

[Buzzer]

Ah, sorry, the table array is in another worksheet so that won't work.

In which case just put my formula in column I as follows:

=IF(H3=TRUE,INDIRECT("A"&ROW())," ")

 

[portlock seagull]

Your call, but doing some English editing using some scripting seems much easier to me than editing text using maths! ... and can be automated...

Believe me, I would if it was feasible. There are many variations on thousands of names. And everyone uses different variations so we've built a table array to look up against and see if company 'x' is exists in it already, regardless of how someone might spell it (former name, with or without punctuation etc etc). But I want to return another value from the table array rather than just see if the company (spelling) already exists, which I've achieved. But you're right, sure there's a more IT savvy way of doing. However I'm not competent enough and the client base is a bit, well, analogue so the currency is spreadsheets!