Vlookup & Concatenate?

[Saxman]

I have a list of names (upper and lower case letters) A2:A26 formatted as txt
in worksheet named 'Txt'.

In columns B:I I have the following headings in the first row:-

Horse
Runs
Wins
Second
Third
Winmoney
Totalmoney
CD

I wish to match the txt data in column A (shorter list up to A26) against
column B (longer list up to B1501). All data in column A is in column B. All
data in column B is in upper case. After matching, I would like column A data
along with the corresponding data in columns C:I dumped in another part of
the worksheet, say K2. Is this possible? The data could then be copied to
another worksheet.

TIA

 

[Guest]

watch out for linewraps - these are one-liners in a cell:

=IF(ISNA(VLOOKUP(A1,B$1:C$1501,2,0)),"",A1 & " " & VLOOKUP(A1,B$1:C$1501,2,0))

or
=IF(ISNA(VLOOKUP(A1,B$1:C$1501,2,0)),"",CONCATENATE(A1,"
",VLOOKUP(A1,B$1:C$1501,2,0))

if you want a more visible indication of no match condition, change ,"", to
something like ,"No Match",

 

[Saxman]

watch out for linewraps - these are one-liners in a cell:

=IF(ISNA(VLOOKUP(A1,B$1:C$1501,2,0)),"",A1 & " " &
VLOOKUP(A1,B$1:C$1501,2,0))

or
=IF(ISNA(VLOOKUP(A1,B$1:C$1501,2,0)),"",CONCATENATE(A1,"
",VLOOKUP(A1,B$1:C$1501,2,0))

if you want a more visible indication of no match condition, change ,"",
to something like ,"No Match",

I guess the above should be (see below)? Do I place it in K2?

=IF(ISNA(VLOOKUP(A2,B$2:C$1501,2,0)),"",CONCATENATE(A2,"
",VLOOKUP(A2,B$2:C$1501,2,0)))

Trying to run the above, I realise that I have another problem. The data in
column A is not an exact match of column B. The original data in column A has
been parsed with the following function.

=LEFT(A2,FIND(" ",A2)-1)

In other words, the original data might be, 'She Looks Lovely 12', but after
parsing will end up, She.

Could I not match up just the first three letters in both columns?

Thanks for your help.

 

[Guest]

Regarding your question about putting the formula in a particular place: it
can actually go anywhere, but if you're going to keep a 1-to-1 relationship
with the entries in column A, then yes, it would go into K2, since they're
starting at row 2.

I believe for the VLOOKUP portion of it you need a 'helper' column. Add a
new column C (leaving the long entries in B and associated values now in D),
then in C, put =LEFT(B2,Find(" ",B2)-1)
That will put same thing that's in your column A into column C. The Vlookup
array reference then changes to ,C$2$1501, and I think it'll work for you.
With the reduced length of the text in C, you may need to watch out for
early matches. Crude example...
In B you have 2 entries: Wins Easily and farther down there is Wins
Everytime
both of those will show up in C as Wins, and the VLOOKUP will only see the
first one in the long list.

 

[Saxman]

Regarding your question about putting the formula in a particular place:
it can actually go anywhere, but if you're going to keep a 1-to-1
relationship with the entries in column A, then yes, it would go into K2,
since they're starting at row 2.

I believe for the VLOOKUP portion of it you need a 'helper' column. Add a
new column C (leaving the long entries in B and associated values now in
D), then in C, put =LEFT(B2,Find(" ",B2)-1)
That will put same thing that's in your column A into column C. The
Vlookup array reference then changes to ,C$2$1501, and I think it'll
work for you. With the reduced length of the text in C, you may need to
watch out for early matches. Crude example...
In B you have 2 entries: Wins Easily and farther down there is Wins
Everytime
both of those will show up in C as Wins, and the VLOOKUP will only see the
first one in the long list.

In my sleep I was thinking similar, but you have done it for me! I will give
this a go.

Thanks for your help.

 

[Saxman]

Regarding your question about putting the formula in a particular place:
it can actually go anywhere, but if you're going to keep a 1-to-1
relationship with the entries in column A, then yes, it would go into K2,
since they're starting at row 2.

I believe for the VLOOKUP portion of it you need a 'helper' column. Add a
new column C (leaving the long entries in B and associated values now in
D), then in C, put =LEFT(B2,Find(" ",B2)-1)
That will put same thing that's in your column A into column C. The
Vlookup array reference then changes to ,C$2$1501, and I think it'll
work for you. With the reduced length of the text in C, you may need to
watch out for early matches. Crude example...
In B you have 2 entries: Wins Easily and farther down there is Wins
Everytime
both of those will show up in C as Wins, and the VLOOKUP will only see the
first one in the long list.

This gets more difficult!

To recap,
Data in column a has been parsed with the function =LEFT(A2,Find(" ",A2)-1)
from another worksheet.

'Group Captain 10' in cell A2 appears as 'Group' with the above function. I
then run a macro which imports similar data in column B. Group Captain in
column B appears as 'GROUP CAPTAIN'. As you suggested, adding a 'helper'
column C with the function =LEFT(B2,Find(" ",B2)-1) works, with 'GROUP
CAPTAIN' appearing as 'GROUP' which is correct.

However, I get #VALUE returned with singular names, ie 'DOUBTLESS', as there
is nothing to parse.

Maybe I ought to add another 'helper' column which would add a number to the
names in column B?

 

[Guest]

Don't make it any more complex than needed, try this
=IF(ISERR(LEFT(A2,FIND(" ",A2)-1)),A2,LEFT(A2,FIND(" ",A2)-1))
instead of the plain
=LEFT(A2,FIND(" ",A2)-1) formula. That will display single words, or 1st
word of multiple word entries.

 

[Guest]

Substitute appropriate column letter where I have A in the formulas -
probably should be B to set up the column in the lookup table.

 

[Guest]

I'm wondering if there isn't another way to do this, to get rid of the
problem with repeated same first words situation created by using the LEFT()
function.

What if you dumped that formula in both column A and in the helper column
and just went ahead and had the full text in A and (as already is) in B. You
could modify the VLOOKUP results to chop off the extra in the results in
column K. The following would do that. It's long, it's not particularly
pretty, but it's a complete formula to concatenate all entries in C:I into
one cell based on a match between column A and column B, but with only 1 word
pulled from the full matched-phrase, be it 1 word long, or several. This
would be out in K2 (remember that it's one long formula, but the editor here
will break it into several):

=IF(ISNA(VLOOKUP(A2,B$2:I$1501,2,0)),"",IF(ISERR(LEFT(A2,FIND("
",A2)-1)),A2,LEFT(A2,FIND(" ",A2)-1)) & " " & VLOOKUP(A2,B$2:I$1501,2,0) & "
" & VLOOKUP(A2,B$2:I$1501,3,0) & " " & VLOOKUP(A2,B$2:I$1501,4,0) & " " &
VLOOKUP(A2,B$2:I$1501,5,0) & " " & VLOOKUP(A2,B$2:I$1501,6,0)& " " &
VLOOKUP(A2,B$2:I$1501,7,0)& " " & VLOOKUP(A2,B$2:I$1501,8,0))

 

[Saxman]

Don't make it any more complex than needed, try this
=IF(ISERR(LEFT(A2,FIND(" ",A2)-1)),A2,LEFT(A2,FIND(" ",A2)-1))
instead of the plain
=LEFT(A2,FIND(" ",A2)-1) formula. That will display single words, or 1st
word of multiple word entries.

I''l try this later. Got to go out for a couple of hours.

Thanks again.

 

[Saxman]

=IF(ISNA(VLOOKUP(A2,B$2:I$1501,2,0)),"",IF(ISERR(LEFT(A2,FIND("
",A2)-1)),A2,LEFT(A2,FIND(" ",A2)-1)) & " " & VLOOKUP(A2,B$2:I$1501,2,0) &
" " & VLOOKUP(A2,B$2:I$1501,3,0) & " " & VLOOKUP(A2,B$2:I$1501,4,0) & " "
& VLOOKUP(A2,B$2:I$1501,5,0) & " " & VLOOKUP(A2,B$2:I$1501,6,0)& " " &
VLOOKUP(A2,B$2:I$1501,7,0)& " " & VLOOKUP(A2,B$2:I$1501,8,0))

Just to recap, using the function below,
=IF(ISERR(LEFT(A2,FIND(" ",A2)-1)),A2,LEFT(A2,FIND(" ",A2)-1))

This returns a single word or first word of a string of words. Works fine.

Column A has runners selected from a racecard (initialed in lower case)
copied from another worksheet in the same workbook (first words only).

Column B has a list of today's runners (in upper case).

Column C (helper) has the formula =IF(ISERR(LEFT(A2,FIND("
",A2)-1)),A2,LEFT(A2,FIND(" ",A2)-1)), and returns the first word in a string
of words from column B.

Columns D:J have all the corresponding data imported with column B.

So now we have two sets of identical data in columns A and C, (although the
text is in lower and upper case).

I now need to match A to C with a VLOOKUP function together with the
corresponding data in separate cells.

In other words we have the horse 'Solent' in cell A2. With parsing we might
have 'SOLENT' in cell C78.

I would like SOLENT (or Solent) placed in cell K2 with its corresponding data
in cells L2:R2 from cells D78:J78. The data needs to be listed in cells
K2:K26 so that matches can be checked.

The function at the start of this post does not appear to function properly.
The data (although returned) is incorrect and appears in one cell, which
makes copying difficult.

 

[Saxman]

The function at the start of this post does not appear to function
properly. The data (although returned) is incorrect and appears in one
cell, which makes copying difficult.

Sorry a correction. The function below is nearly there.

=IF(ISNA(VLOOKUP(A2,B$2:I$1501,2,0)),"",IF(ISERR(LEFT(A2,FIND("
",A2)-1)),A2,LEFT(A2,FIND(" ",A2)-1)) & " " & VLOOKUP(A2,B$2:I$1501,2,0) & ""
& VLOOKUP(A2,B$2:I$1501,3,0) & " " & VLOOKUP(A2,B$2:I$1501,4,0) & " "
&VLOOKUP(A2,B$2:I$1501,5,0) & " " & VLOOKUP(A2,B$2:I$1501,6,0)& " "
&VLOOKUP(A2,B$2:I$1501,7,0)& " " & VLOOKUP(A2,B$2:I$1501,8,0))

As an example I have the following data in these cells:-
A2 Solent
B236 SOLENT
C236 SOLENT (helper, parsed)
D236 22
E236 4
F236 3
G236 2
H236 35127
I236 69401
J236 D

K2 with your function returns (all in K2):-
Solent SOLENT22 4 3 2 35127 69401
The data in J236 is missing. The function needs tweaking to include this.

For some reason, from 14 runners, only three were returned in the K column
and yet all the horses match in both columns A and C exactly?

 

[Guest]

Look at Excel's Help on VLOOKUP, I think that will explain a lot to you. For
the first part of your problem (missing information from column J) - Change
all references from B$2:I$1501 to B$2:J$1501 so that column J is included
as part of the lookup table. Then add one more
& " " & VLOOKUP(A2,B$2:J$1501,9,0)
right between the last two )) in the existing formula.

Now, we're confusing apples and oranges here or I'm getting confused. You
mention earlier that both column A and C have the =LEFT() formula in them??
The formula that I posted at the top of this sequence assumes that the new C
helper column DOES NOT EXIST, and that column A has the complete text from
its source in it! The cut-down to the first word/one word only takes place
within the long multiple-VLOOKUP() formula. I suspect that the 3 returns out
of 14 you got were from matches to single word entries in column B: so
columns A, B and C all have the same one word in them.

BASED ON YOUR RECAP, this is what you need at this point:
IF you are still using the one-word entries in A and are still using the
helper column in C, then DO not add another VLOOKUP() to the long
multi-VLOOKUP() formula, simply change all instances of B$2:I$1501 to become
C$2:J$1501 and it should work.

if you want to put the various values from D:J into K:R, then start with
this formula in K2 (assumes helper column C)
=IF(ISNA(VLOOKUP($A2,$C$2:$J$1501,2,0)),"",VLOOKUP($A2,$C$2:$J$1501,2,0))
then fill it to the right into columns L:R on row 2.
Then starting in L2, change the ,2,0) portion of the formula to ,3,0)
in M2, change ,2,0) to ,4,0)
in N2, change ,2,0) to ,5,0)
see the pattern emerging? If not, ...
in O2, change ,2,0) to ,6,0)
in P2, change ,2,0) to ,7,0)
in Q2, change ,2,0) to ,8,0)
and finally in R2, change ,2,0) to ,9,0)
now you can fill the formulas down as far as you need to.

 

[Saxman]

Look at Excel's Help on VLOOKUP, I think that will explain a lot to you.
For the first part of your problem (missing information from column J) -
Change all references from B$2:I$1501 to B$2:J$1501 so that column J is
included as part of the lookup table. Then add one more
& " " & VLOOKUP(A2,B$2:J$1501,9,0)
right between the last two )) in the existing formula.

Now, we're confusing apples and oranges here or I'm getting confused. You
mention earlier that both column A and C have the =LEFT() formula in
them?? The formula that I posted at the top of this sequence assumes that
the new C helper column DOES NOT EXIST, and that column A has the complete
text from its source in it! The cut-down to the first word/one word only
takes place within the long multiple-VLOOKUP() formula. I suspect that the
3 returns out of 14 you got were from matches to single word entries in
column B: so columns A, B and C all have the same one word in them.

Column A is copied from another worksheet which has this function in A2.
=IF(ISERR(LEFT(A2,FIND(" ",A2)-1)),A2,LEFT(A2,FIND(" ",A2)-1))

Column C has this in C2 (which it gets from B2.
=IF(ISERR(LEFT(B2,FIND(" ",B2)-1)),B2,LEFT(B2,FIND(" ",B2)-1))

BASED ON YOUR RECAP, this is what you need at this point:
IF you are still using the one-word entries in A and are still using the
helper column in C, then DO not add another VLOOKUP() to the long
multi-VLOOKUP() formula, simply change all instances of B$2:I$1501 to
become C$2:J$1501 and it should work.

if you want to put the various values from D:J into K:R, then start with
this formula in K2 (assumes helper column C)
=IF(ISNA(VLOOKUP($A2,$C$2:$J$1501,2,0)),"",VLOOKUP($A2,$C$2:$J$1501,2,0))
then fill it to the right into columns L:R on row 2.
Then starting in L2, change the ,2,0) portion of the formula to ,3,0)
in M2, change ,2,0) to ,4,0)
in N2, change ,2,0) to ,5,0)
see the pattern emerging? If not, ...
in O2, change ,2,0) to ,6,0)
in P2, change ,2,0) to ,7,0)
in Q2, change ,2,0) to ,8,0)
and finally in R2, change ,2,0) to ,9,0)
now you can fill the formulas down as far as you need to.

I really appreciate your help and patience.

I will give this a go in the morning, as it's bedtime in the UK!

This looks like it will work. Can't wait! I'll keep you posted.

 

[Saxman]

if you want to put the various values from D:J into K:R, then start with
this formula in K2 (assumes helper column C)
=IF(ISNA(VLOOKUP($A2,$C$2:$J$1501,2,0)),"",VLOOKUP($A2,$C$2:$J$1501,2,0))
then fill it to the right into columns L:R on row 2.
Then starting in L2, change the ,2,0) portion of the formula to ,3,0)
in M2, change ,2,0) to ,4,0)
in N2, change ,2,0) to ,5,0)
see the pattern emerging? If not, ...
in O2, change ,2,0) to ,6,0)
in P2, change ,2,0) to ,7,0)
in Q2, change ,2,0) to ,8,0)
and finally in R2, change ,2,0) to ,9,0)
now you can fill the formulas down as far as you need to.

The above works a treat, thanks.

Just one minor problem though.

In column B (imported data) on this particular day, there are horses namely
CELTIC SPA and CELTIC SPIRIT. Both, naturally get parsed to CELTIC in the
'helper' column C using the function,

=IF(ISERR(LEFT(B2,FIND(" ",B2)-1)),B2,LEFT(B2,FIND(" ",B2)-1))

By default the information returned will always be the first in the listing.
I could overcome this by doing a manual check.

As described before data in column A is derived from a different source than
column B. The original raw data in A looks like this for example:-
Solent 36
Group Captain 29
Before You Go 23
Purple Moon 37
Halla San 29
Strategic Mount 324
John Terry 16
Celtic Spirit 32
Misty Dancer 36
Mull Of Dubai 24
Dan Dare 29
High Treason 16
Instructor 84 (59J)
Masterofthecourt 11

From the other source it is words only, all capitalised.

Is there not a function that would omit the numbers and brackets, so that all
the lettering remained? This would avoid confusion regarding VLOOKUP.

If this were not possible, could conditional formatting be applied to
'helper' column C which would highlight similarities?

Thanks so much again.

You made my day!