Excel - Concatenate fields through Vlookup...or something
June 9, 2006 12:17 PM Subscribe
Excel Filter: Concatenating fields through VLOOKUP. Is it possible? What I'm trying to accomplish inside...
I have two Worksheets in my Excel Workbook, DETAILS and APPS. DETAILS is an export from a discovery application containing the software installed on each computer. It looks something like this:
I have two Worksheets in my Excel Workbook, DETAILS and APPS. DETAILS is an export from a discovery application containing the software installed on each computer. It looks something like this:
A B C D Serial# User Location Title 123456 Smith, John Cleveland Adobe Photoshop 123456 Smith, John Cleveland Microsoft Project 123456 Smith, John Cleveland Adobe Standard 123456 Smith, John Cleveland SigmaPlot 234567 Doe, Jane Chicago SigmaScan 234567 Doe, Jane Chicago Lotus Notes 345678 Williams, Ted Cleveland Microsoft Visio 456789 Ruth, Babe Atlanta Microsoft Project 456789 Ruth, Babe Atlanta Adobe PhotoshopBy some magic use of the VLOOKUP, CONCATENATE, and IF statements (and/or whatever else needs to be used to get me there) I would like to populate my APPS worksheet. My goal is to list the unique serial #'s and then create a "Title" column which concatenates the "Title" field corepsonding to the "Serial#" field from the DETAILS worksheet. I see it looking soemthing like this:
A B Serial# Title 123456 Adobe Photoshop;Microsoft Project;Adobe Standard;SigmaPlot 234567 SigmaScan;Lotus Notes 345678 Microsoft Visio 456789 Microsoft Project;Adobe PhotoshopDoes this make sense? Is it possible? Is there an easier way? My end goal is to be able to bring the "Title" fields from the APPS worksheet into an MS Word Mail Merge, so I'm open to hearing other suggestions that would accomplish my end goal. Any help would be greatly appreciated.
Best answer: Here's how I would do it:
a) create a pivot table from your data, with serial# as the rows, and title as the columns. (Note: this assumes the number of distinct software titles is less than ~256, the maximum number of columns in Excel. And you probably need a few extra columns for the pivot table, so it's probably really a maximum of 254 or 253 or something like that.)
b) create a second table (probably in a different worksheet) the same size as the pivot table. You'll copy the row headers and column headers from the pivot table into this one, but in the body, instead of the numbers in the pivot table, use an IF referring back to the pivot table to insert the appropriate Title, followed by a semicolon, directly into the table itself if the value in the pivot table is non-zero, and leaving the cell blank if the corresponding cell in the pivot table is zero.
c) Concatenate all the cells from a row of this second table into a single cell. You can also remove the final trailing semicolon at this point if you want.
That's how I would do it; it's entirely possible there's an easier way, though. I don't know how much experience you have with Excel, so if you need more detailed instructions for doing the above, let me know.
posted by DevilsAdvocate at 12:34 PM on June 9, 2006
a) create a pivot table from your data, with serial# as the rows, and title as the columns. (Note: this assumes the number of distinct software titles is less than ~256, the maximum number of columns in Excel. And you probably need a few extra columns for the pivot table, so it's probably really a maximum of 254 or 253 or something like that.)
b) create a second table (probably in a different worksheet) the same size as the pivot table. You'll copy the row headers and column headers from the pivot table into this one, but in the body, instead of the numbers in the pivot table, use an IF referring back to the pivot table to insert the appropriate Title, followed by a semicolon, directly into the table itself if the value in the pivot table is non-zero, and leaving the cell blank if the corresponding cell in the pivot table is zero.
c) Concatenate all the cells from a row of this second table into a single cell. You can also remove the final trailing semicolon at this point if you want.
That's how I would do it; it's entirely possible there's an easier way, though. I don't know how much experience you have with Excel, so if you need more detailed instructions for doing the above, let me know.
posted by DevilsAdvocate at 12:34 PM on June 9, 2006
Is this something that will be done once? Or will it be an ongoing process to keep updating this?
I'm thinking a macro could deal with this pretty well; I can sketch out something (just not now, sorry).
Does the Apps page already have a list of all the unique IDs, or does that have to be compiled as you go, too?
posted by inigo2 at 12:45 PM on June 9, 2006
I'm thinking a macro could deal with this pretty well; I can sketch out something (just not now, sorry).
Does the Apps page already have a list of all the unique IDs, or does that have to be compiled as you go, too?
posted by inigo2 at 12:45 PM on June 9, 2006
Response by poster: Thanks for your quick responses. To answer the questions...
Kwantsar: I'm working with 150-200 titles.
DevilsAdvocate: I've lost on you on B) a little bit, but I'm seeing potential as I got it in the Pivot table. I'm not good with IF statements so (if you can) more detail would be very helpful. I'm going to keep playing and see if I can work through your instructions.
inigo2: It'll be more of a one time thing. I thought a macro would probably be able to pull it off, but I wouldn't even know where to start. I don't expect anybody to code a macro for this, so don't sweat it (though I appreciate the consideration). I am using the Serial# as the unique ID on the APPS sheet, and yes, they are all present and ready to be used (I hope that's what you were asking).
Thanks again for your help.
posted by bwilms at 12:55 PM on June 9, 2006
Kwantsar: I'm working with 150-200 titles.
DevilsAdvocate: I've lost on you on B) a little bit, but I'm seeing potential as I got it in the Pivot table. I'm not good with IF statements so (if you can) more detail would be very helpful. I'm going to keep playing and see if I can work through your instructions.
inigo2: It'll be more of a one time thing. I thought a macro would probably be able to pull it off, but I wouldn't even know where to start. I don't expect anybody to code a macro for this, so don't sweat it (though I appreciate the consideration). I am using the Serial# as the unique ID on the APPS sheet, and yes, they are all present and ready to be used (I hope that's what you were asking).
Thanks again for your help.
posted by bwilms at 12:55 PM on June 9, 2006
That looks to me like a problem you'd be better off solving in Access or any other DB where you can do a JOIN on the tables.
posted by AmbroseChapel at 2:25 PM on June 9, 2006
posted by AmbroseChapel at 2:25 PM on June 9, 2006
Best answer: OK, I'm using your sample data above. The column headers are in cells A1-D1 of Sheet1, and the actual data in A2-D10.
The pivot table is in Sheet2. The serial numbers are in A5-A8; the Titles are in B4-H4.
Now, create a new worksheet, if there's not already another blank one--let that be Sheet3.
Copy/paste cells A5-A8 and B4-H4 of Sheet2 into the corresponding cells of Sheet3.
Now, in B5, enter:
Grab the lower right corner of B5 and drag it to H5, then to H8, to copy the formula into all cells from B5-H8.
In I5, enter:
Grab the corner of I5 and drag it down to I8 to fill all the cells.
If you want to get rid of the trailing semicolon, you can enter
posted by DevilsAdvocate at 3:28 PM on June 9, 2006
The pivot table is in Sheet2. The serial numbers are in A5-A8; the Titles are in B4-H4.
Now, create a new worksheet, if there's not already another blank one--let that be Sheet3.
Copy/paste cells A5-A8 and B4-H4 of Sheet2 into the corresponding cells of Sheet3.
Now, in B5, enter:
=IF(Sheet2!B5>0,B$4&";","")(Basic format of the IF instruction: the first argument is the test, the second argument is what the value is if the test is true, and the third argument is what the value is if the test is false.)
Grab the lower right corner of B5 and drag it to H5, then to H8, to copy the formula into all cells from B5-H8.
In I5, enter:
=B5&C5&D5&E5&F5&G5&H5or you can also use
=CONCATENATE(B5,C5,D5,E5,E5,F5,G5,H5)(This is going to be the sucky part when you do it for real, because you'll have to enter all 150+ cells in the row. Excel won't let you do =CONCATENATE(B5:H5), which is just stupid. At least you only have to do it once.)
Grab the corner of I5 and drag it down to I8 to fill all the cells.
If you want to get rid of the trailing semicolon, you can enter
=LEFT(I5,LEN(I5)-1)in J5 and then drag the corner of that down to J8.
posted by DevilsAdvocate at 3:28 PM on June 9, 2006
You're good, devilsadvocate. Damn good. But the "sucky part" doesn't have to suck.
1. use this in A1 of another sheet: =IF(COLUMN(A1) > 26, CHAR(INT((COLUMN(A1) - 1)/ 26) + 64) & CHAR(MOD(COLUMN(A1) - 1,26) + 65), CHAR(COLUMN(A1) + 64)) . Drag across 150 cells.
2. In cell A2 immediately beneath, type =A1&5 . Drag across 150 cells.
3. Copy those as unformatted cells into word (paste special again).
4. Find and replace "5" with "5,"
5. Copy.
6. Insert into your CONCATENATE.
Step one courtesy of microsoft.public.excel.misc. I'm sure there's an easier way, but this is pretty easy.
posted by Kwantsar at 4:11 PM on June 9, 2006
1. use this in A1 of another sheet: =IF(COLUMN(A1) > 26, CHAR(INT((COLUMN(A1) - 1)/ 26) + 64) & CHAR(MOD(COLUMN(A1) - 1,26) + 65), CHAR(COLUMN(A1) + 64)) . Drag across 150 cells.
2. In cell A2 immediately beneath, type =A1&5 . Drag across 150 cells.
3. Copy those as unformatted cells into word (paste special again).
4. Find and replace "5" with "5,"
5. Copy.
6. Insert into your CONCATENATE.
Step one courtesy of microsoft.public.excel.misc. I'm sure there's an easier way, but this is pretty easy.
posted by Kwantsar at 4:11 PM on June 9, 2006
I can't help but think there's an Array formula that can do this all in one go. I'll reply if I can figure it out.
posted by junesix at 9:39 PM on June 9, 2006
posted by junesix at 9:39 PM on June 9, 2006
Nicely done, Kwantsar.
posted by DevilsAdvocate at 6:38 AM on June 10, 2006
posted by DevilsAdvocate at 6:38 AM on June 10, 2006
Response by poster: DevilsAdvocate: Thanks for taking the extra time. I started to grasp your first comment and had it finished by the time you posted the second.
For what it's worth, once I replaced the pivot counts using the IF statement, I copied and pasted into a notepad window and saved it as a text document. While in the document, I did a REPLACE on the tab spacing. Then I opened it in Excel, which placed each row of text into a separate field, essentially concatenating it for me.
A few MID statements later, I had the information I needed in separate fields. It takes about 5 minutes for the whole process and it was well worth it. Thanks again.
posted by bwilms at 8:34 AM on June 12, 2006
For what it's worth, once I replaced the pivot counts using the IF statement, I copied and pasted into a notepad window and saved it as a text document. While in the document, I did a REPLACE on the tab spacing. Then I opened it in Excel, which placed each row of text into a separate field, essentially concatenating it for me.
A few MID statements later, I had the information I needed in separate fields. It takes about 5 minutes for the whole process and it was well worth it. Thanks again.
posted by bwilms at 8:34 AM on June 12, 2006
This thread is closed to new comments.
posted by Kwantsar at 12:31 PM on June 9, 2006