Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
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)),"",CONCATENAT E(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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
On 28/07/2007 15:04:01, JLatham wrote:
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)),"",CONCATENAT E(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)),"",CONCATENAT E(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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
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:D$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" wrote: On 28/07/2007 15:04:01, JLatham wrote: 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)),"",CONCATENAT E(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)),"",CONCATENAT E(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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
On 29/07/2007 01:43:58, JLatham wrote:
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:D$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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
On 29/07/2007 01:43:58, JLatham wrote:
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:D$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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
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. "Saxman" wrote: On 29/07/2007 01:43:58, JLatham wrote: 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:D$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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
Substitute appropriate column letter where I have A in the formulas -
probably should be B to set up the column in the lookup table. "Saxman" wrote: On 29/07/2007 01:43:58, JLatham wrote: 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:D$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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
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(L EFT(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" wrote: 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
On 29/07/2007 13:12:00, JLatham wrote:
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
On 29/07/2007 13:43:58, JLatham wrote:
=IF(ISNA(VLOOKUP(A2,B$2:I$1501,2,0)),"",IF(ISERR(L EFT(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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
On 29/07/2007 19:24:14, "Saxman" wrote:
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(L EFT(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? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
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" wrote: On 29/07/2007 19:24:14, "Saxman" wrote: 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(L EFT(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? |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
On 29/07/2007 21:13:58, JLatham wrote:
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. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup & Concatenate?
On 29/07/2007 21:13:58, JLatham wrote:
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup, Concatenate, Format | Excel Discussion (Misc queries) | |||
Concatenate and Vlookup | Excel Worksheet Functions | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel | |||
Using Concatenate inside a vlookup | Excel Worksheet Functions | |||
Can you use Concatenate with the If function with vlookup in the i | Excel Worksheet Functions |