Got something to say or just want fewer pesky ads? Join us... 😊

Excel help please - any wizards on NSC?!



portlock seagull

Why? Why us?
Jul 28, 2003
16,979
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.
 




Buzzer

Languidly Clinical
Oct 1, 2006
26,121
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?
 




KingKev

Well-known member
Jun 16, 2011
867
Hove (actually)
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

Well-known member
Mar 7, 2012
894
Saff of the River
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

Why? Why us?
Jul 28, 2003
16,979
Screen Shot 2017-04-11 at 16.51.21.png

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

Well-known member
Jan 25, 2013
5,826
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

Why? Why us?
Jul 28, 2003
16,979
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

Why? Why us?
Jul 28, 2003
16,979
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

Active member
Jul 6, 2004
120
Newcastle upon Tyne
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

Why? Why us?
Jul 28, 2003
16,979
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

Active member
Feb 13, 2011
165
Richmond
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...
 
Last edited:


portlock seagull

Why? Why us?
Jul 28, 2003
16,979
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

Active member
Feb 13, 2011
165
Richmond
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

Languidly Clinical
Oct 1, 2006
26,121
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 ("").

2ni4s21.jpg



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


portlock seagull

Why? Why us?
Jul 28, 2003
16,979
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

Why? Why us?
Jul 28, 2003
16,979
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 ("").

2ni4s21.jpg



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.
 






portlock seagull

Why? Why us?
Jul 28, 2003
16,979
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!
 



Paying the bills

Latest Discussions

Paying the bills

Paying the bills

Paying the bills

Albion and Premier League latest from Sky Sports


Top
Link Here