Excel - Concatenate fields through Vlookup...or something

June 9, 2006 12:17 PM

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:

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 Photoshop

By 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 Photoshop

Does 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.

posted by bwilms to computers & internet (10 comments total)

How many titles are you working with?
posted by Kwantsar at 12:31 PM on June 9, 2006

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

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

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

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

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:

=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&H5

or 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

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

Nicely done, Kwantsar.
posted by DevilsAdvocate at 6:38 AM on June 10, 2006

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