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

Excel help please - any wizards on NSC?!



Goldstone1976

We Got Calde in!!
Helpful Moderator
NSC Patreon
Apr 30, 2013
13,766
Herts
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.

Hmm. I thought Buzzer had it there. Time to call in the expert, I think: [MENTION=451]BensGrandad[/MENTION] , can you help him out please?
 


clapham_gull

Legacy Fan
Aug 20, 2003
25,179
Its the sort of stuff I get involved in, but not usually with Excel.

I'm not quite sure what you are after but I generaly use something like Jaccard Coefficient in conjuction with K Grams...

..after cleaning the strings.

It's best to work out that data upfront.

I'm on holiday at the mo, but PM me after Easter if it's some sort of fuzzy matching you are after.

The above technique will take a single phrase and compare it's simularity against a number of others.

.. Basically spell checking which is just maths really.

The long list of words is basically a dictionary.

I tend to use SQL more than Excel but it's the same type of technique.

Sent from my LG-K520 using Tapatalk
 


portlock seagull

Why? Why us?
Jul 28, 2003
16,979
In which case just put my formula in column I as follows:

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

You Sir, are a genius! Thank you Lord Buzzer, I now need to replace the "A"&ROW(... bit with my table array on another s/s, so it's looking at that value instead. But I think I can do that and hopefully it'll compare the two workbooks and returning said value correctly.
 




North East Seagull

Active member
Jul 6, 2004
120
Newcastle upon Tyne
Hi Portlock,
Did you solve this? I was fascinated by your challenge and have come up with another way to do it. It is a two step process but dead easy (has to be - my Excel skills are weak). Will post only if not solved by other posters advice.
 




father_and_son

Well-known member
Jan 23, 2012
4,646
Under the Police Box
HANDY TIP


VLOOKUP is vastly over-rated given that it takes more CPU and is less flexible that INDEX+MATCH...

So Instead of = VLOOKUP(<SearchCell>,<WholeArray>,<Offset of Search Col>,False)

it is better to be in the habit of using... =INDEX(<ReturnCol>,MATCH(<SearchCell>,<SearchCol>,0)) ...because it uses less CPU time (only makes a difference on VERY large spreadsheets) but the Search Column and Return Column don't even have to be in the same workbook, never mind the same array (so as long as they are the same size)!
 




portlock seagull

Why? Why us?
Jul 28, 2003
16,979
[MENTION=5200]Buzzer[/MENTION] [MENTION=13773]Martin[/MENTION]Tyler [MENTION=865]clapham_gull[/MENTION]

Hi again, very grateful for all the support so far but applying the above to my actual data isn't working because I'm on different sheets. I've been banging head against wall a lot this morning!! So decided to present screenshots of exactly how it's laid out so I can copy the formula exactly as it's given.

Sheet 1 is where I'm trying to return in col.I the number of points (shown in Sheet 2, col.A) if the name of the team shown in Col.G of Sheet 1, exists in the table array of different team spellings shown in Sheet 2, col.B to E)

Sheet 1 looks like this:
Screen Shot 2017-04-12 at 11.40.36.png

Sheet 2 looks like this:
Screen Shot 2017-04-12 at 11.41.19.png

In summary, I've found the spelling variation in the Sheet 2's table array, if there's a 'True' value in col.H on Sheet 1. Adjacent to that, I now want it to retrieve the corresponding number of points on sheet 2 if it's found the spelling exists.

My thanks so far, sorry but I've been applying all the help but not quite getting there :(
 






portlock seagull

Why? Why us?
Jul 28, 2003
16,979
In which case just put my formula in column I as follows:

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

Hi Buzzer, see my post 28 - so sorry, I just couldn't get that to work given the data is on a 2nd sheet. I've laid out as per my actual s/s, are you able to assist still please?
 


portlock seagull

Why? Why us?
Jul 28, 2003
16,979
Hmm. I thought Buzzer had it there. Time to call in the expert, I think: [MENTION=451]BensGrandad[/MENTION] , can you help him out please?

And thanking you for assisting to! :)
 




portlock seagull

Why? Why us?
Jul 28, 2003
16,979
HANDY TIP


VLOOKUP is vastly over-rated given that it takes more CPU and is less flexible that INDEX+MATCH...

So Instead of = VLOOKUP(<SearchCell>,<WholeArray>,<Offset of Search Col>,False)

it is better to be in the habit of using... =INDEX(<ReturnCol>,MATCH(<SearchCell>,<SearchCol>,0)) ...because it uses less CPU time (only makes a difference on VERY large spreadsheets) but the Search Column and Return Column don't even have to be in the same workbook, never mind the same array (so as long as they are the same size)!

Thanks - but how would you apply this to resolve my issue (see post 28)
 




portlock seagull

Why? Why us?
Jul 28, 2003
16,979
I'll have a look today see if I can get it working from what you shown there in28

Thank you so much! Sorry to put you (all) to so much trouble. It's very much appreciated.
 




father_and_son

Well-known member
Jan 23, 2012
4,646
Under the Police Box
Thanks - but how would you apply this to resolve my issue (see post 28)

=INDEX('Sheet2'!A:A,MAX(IFERROR(MATCH('Sheet1'!G2,'Sheet2'!B:B,0),0),IFERROR(MATCH('Sheet1'!G2,'Sheet2'!C:C,0),0),IFERROR(MATCH('Sheet1'!G2,'Sheet2'!D:D,0),0),IFERROR(MATCH('Sheet1'!G2,'Sheet2'!E:E,0),0)))


So:
1. look for G2 ("Brighton&HA") in Column B and if it isn't there return a zero, else return it's row number
2. look for G2 ("Brighton&HA") in Column C and if it isn't there return a zero, else return it's row number
3. look for G2 ("Brighton&HA") in Column D and if it isn't there return a zero, else return it's row number
4. look for G2 ("Brighton&HA") in Column E and if it isn't there return a zero, else return it's row number

5. Take the Maximum value of the above... so, providing what you are looking for is in one of the columns, you find the row number it's in. If it isn't in any of the columns then you will get zero... which gives an error in the final step.

6. Finally, go down Column A to the row determined in step 5 and you will have the points for "Brighton&HA"



[The smiley face is <Colon Capital D>. I know Excel but I don't know how to turn off smiley's in NSC]
 
Last edited:


Publius Ovidius

Well-known member
Jul 5, 2003
45,919
at home
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.

Speaking as a ITIL trained Change manager, switch it off and on again

You have been charged £500 an hour for this information
 


father_and_son

Well-known member
Jan 23, 2012
4,646
Under the Police Box
=INDEX('Sheet2'!A:A,MAX(IFERROR(MATCH('Sheet1'!G2,'Sheet2'!B:B,0),0),IFERROR(MATCH('Sheet1'!G2,'Sheet2'!C:C,0),0),IFERROR(MATCH('Sheet1'!G2,'Sheet2'!D:D,0),0),IFERROR(MATCH('Sheet1'!G2,'Sheet2'!E:E,0),0)))


So:
1. look for G2 ("Brighton&HA") in Column B and if it isn't there return a zero, else return it's row number
2. look for G2 ("Brighton&HA") in Column C and if it isn't there return a zero, else return it's row number
3. look for G2 ("Brighton&HA") in Column D and if it isn't there return a zero, else return it's row number
4. look for G2 ("Brighton&HA") in Column E and if it isn't there return a zero, else return it's row number

5. Take the Maximum value of the above... so, providing what you are looking for is in one of the columns, you find the row number it's in. If it isn't in any of the columns then you will get zero... which gives an error in the final step.

6. Finally, go down Column A to the row determined in step 5 and you will have the points for "Brighton&HA"



[The smiley face is <Colon Capital D>. I know Excel but I don't know how to turn off smiley's in NSC]

Been thinking.... this only works if you have a manageable number of columns... There is no easy way to do this that I know of if you have an array of company names that is extremely large...

The easiest solution would be to build a simple custom formula to do what you want to do...

Turn on the Developer Tab (in Excel Options)... Double-Click Visual Basic... On the right, where it say "Project - VBAProject", right click, select "Insert" and "Module"

Add the following to the module...


Public Function FindName(ByVal rRange As Range, sText As String) As Long

Dim lCol As Long, lRow As Long

For lCol = 0 To rRange.Columns.Count - 1
lRow = 0
On Error Resume Next
lRow = Excel.WorksheetFunction.Match(sText, Range(Cells(rRange.Row, rRange.Column + lCol), Cells(rRange.Row + rRange.Rows.Count, rRange.Column + lCol)), 0)
On Error GoTo 0
If lRow > 0 Then
FindName = lRow
Exit Function
End If
Next lCol

End Function








You now have a new function available which returns the row number of a value when looking at a very large array.

now your formula would be

=INDEX('Sheet2'!A:A,FindName('Sheet2'!B1:IV65325,'Sheet1'!G2))
 


portlock seagull

Why? Why us?
Jul 28, 2003
16,979
=INDEX('Sheet2'!A:A,MAX(IFERROR(MATCH('Sheet1'!G2,'Sheet2'!B:B,0),0),IFERROR(MATCH('Sheet1'!G2,'Sheet2'!C:C,0),0),IFERROR(MATCH('Sheet1'!G2,'Sheet2'!D:D,0),0),IFERROR(MATCH('Sheet1'!G2,'Sheet2'!E:E,0),0)))


So:
1. look for G2 ("Brighton&HA") in Column B and if it isn't there return a zero, else return it's row number
2. look for G2 ("Brighton&HA") in Column C and if it isn't there return a zero, else return it's row number
3. look for G2 ("Brighton&HA") in Column D and if it isn't there return a zero, else return it's row number
4. look for G2 ("Brighton&HA") in Column E and if it isn't there return a zero, else return it's row number

5. Take the Maximum value of the above... so, providing what you are looking for is in one of the columns, you find the row number it's in. If it isn't in any of the columns then you will get zero... which gives an error in the final step.

6. Finally, go down Column A to the row determined in step 5 and you will have the points for "Brighton&HA"



[The smiley face is <Colon Capital D>. I know Excel but I don't know how to turn off smiley's in NSC]

Hi and thank you, what a bit of code you've written!!

Good news - it worked correctly! But only up to a point, so I'll come clean and present the real data so you can see exactly what I'm talking about (I tried to make it 'footy like' but probably best I just show the RAW data.

Sheet 1:
Screen Shot 2017-04-12 at 16.36.44.png

Sheet 2:
Screen Shot 2017-04-12 at 16.37.12.png

Applying your algorithm, you'll see it returned the correct Company ID (from sheet 2) for the first 3 rows in Sheet 1 i.e. A1Bacon = 406 in sheet 2 and it's returned '406' into Sheet 1 (cells I2,I3&I4)

However, when you move onto the next company - called AAK - in Sheet 1(cell G5), the ID being returned from Sheet 2 is 623 - when it should be 126 (see cell A8). Similarly, AAKFoods (same company, different spelling) I also need to return 126 as the company ID - and it does! However, when move into the next company - called ABWorldFoods - it returns multiple Company IDs (the correct one should be 478, if you look at Sheet2)

Now I've explained properly using the real data, is this something you can resolve please? Or is there even a new way of doing things.

The basic outline of my problem is that dozens of different businesses use the same suppliers but all have different spellings and variations (use or not of punctuation, initials, suffixes, abbreviations etc) which makes comparisons to see if we're already connected to them v.tricky. Hence why we've a table array with multiple spelling variations in Sheet2 to compare Sheet1 against (and return 'true' if it does exist in Sheet2). I just need it to also return the correct Company ID number alongside.
 




portlock seagull

Why? Why us?
Jul 28, 2003
16,979
Been thinking.... this only works if you have a manageable number of columns... There is no easy way to do this that I know of if you have an array of company names that is extremely large...

The easiest solution would be to build a simple custom formula to do what you want to do...

Turn on the Developer Tab (in Excel Options)... Double-Click Visual Basic... On the right, where it say "Project - VBAProject", right click, select "Insert" and "Module"

Add the following to the module...


Public Function FindName(ByVal rRange As Range, sText As String) As Long

Dim lCol As Long, lRow As Long

For lCol = 0 To rRange.Columns.Count - 1
lRow = 0
On Error Resume Next
lRow = Excel.WorksheetFunction.Match(sText, Range(Cells(rRange.Row, rRange.Column + lCol), Cells(rRange.Row + rRange.Rows.Count, rRange.Column + lCol)), 0)
On Error GoTo 0
If lRow > 0 Then
FindName = lRow
Exit Function
End If
Next lCol

End Function








You now have a new function available which returns the row number of a value when looking at a very large array.

now your formula would be

=INDEX('Sheet2'!A:A,FindName('Sheet2'!B1:IV65325,'Sheet1'!G2))

Hi, I've tried but error message keeps coming back. Developer and Macros is an area I'm really not familiar with though (and it's slightly different set up on a Mac). I read this only after my post 38 mind.
 


portlock seagull

Why? Why us?
Jul 28, 2003
16,979
I'll have a look today see if I can get it working from what you shown there in28

See 38 for the actual raw data (assuming you're able to help still) - have to say, this is a bit of a bugger. Fuzzy logic and some!
 



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