Concatenate info from columns into one row.

Hie, can anyone help with the following:
COL A        COL B     COL C       COL D
Unit 1         Unit 1     3B             Unit 1: 3B, 5LH, 6RH
Unit 2         Unit 1     5LH           Unit 2: 4B, 6LH, 7RH
Unit 3         Unit 1     6RH          Unit 3: 5B, 4LH, 9RH
Unit 4         Unit 2     4B             Unit 4
                 Unit 2     6LH
                 Unit 2     7RH 
                 Unit 3     5B
                 Unit 3     4LH
                 Unit 3     9RH
                 Unit 4

What i've got are 4 unique units, each of which have a handing assigned to 
them.
I need to concatenate the info in each column against each unique unit, 
which gives the info on one row for each unit (COL D).
This is just an example. I may have unique units which have no handing info 
against them (e.g. Unit 4) and therefore will appear on there own in COL D 
with no handing info against them etc.

A macro to automate this process would be greatly appreciated.
Thanks
-- 
jj
0
Utf
12/7/2007 1:10:00 PM
access 16762 articles. 3 followers. Follow

1 Replies
1978 Views

Similar Articles

[PageSpeed] 39

Could you explain a bit more about how you get from the three columns of
data (A, B, C) to the combination you are showing in the 4th column (D)?

And are you trying to do this in a query, or is this in a table?

Is this a spreadsheet?  (This newsgroup supports Microsoft Access, the
relational database.)

-- 
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/



"jbjtc" <jbjtc@discussions.microsoft.com> wrote in message
news:D14A5A3F-2EC8-4521-884B-B6FEC5242B06@microsoft.com...
> Hie, can anyone help with the following:
> COL A        COL B     COL C       COL D
> Unit 1         Unit 1     3B             Unit 1: 3B, 5LH, 6RH
> Unit 2         Unit 1     5LH           Unit 2: 4B, 6LH, 7RH
> Unit 3         Unit 1     6RH          Unit 3: 5B, 4LH, 9RH
> Unit 4         Unit 2     4B             Unit 4
>                  Unit 2     6LH
>                  Unit 2     7RH
>                  Unit 3     5B
>                  Unit 3     4LH
>                  Unit 3     9RH
>                  Unit 4
>
> What i've got are 4 unique units, each of which have a handing assigned to
> them.
> I need to concatenate the info in each column against each unique unit,
> which gives the info on one row for each unit (COL D).
> This is just an example. I may have unique units which have no handing
info
> against them (e.g. Unit 4) and therefore will appear on there own in COL D
> with no handing info against them etc.
>
> A macro to automate this process would be greatly appreciated.
> Thanks
> -- 
> jj

0
Jeff
12/7/2007 3:00:17 PM
Reply:

Similar Artilces:

show another column when hovering on a point in a chart
Is it possible to show another column when you hover on a point? I have 3 columns, A, B, and C for a scatter plot I have B and C as x and y coordinates of a point, and I'd like to have Excel show A when I hover on the point. thanks, Wei ...

Can the column index in a cell address be made variable?
Hi, To refer to a cell with a variable row number, we can just code it as, for example, Dim i As Interger i=234 Range("A" & i).Select To refer to a cell with a variable column index, it seems not that easy because the column index must be explicitly specified in a cell address. So, if I want to go to the j th column on the 2nd row or j columns to the right of cell AA3, is there a quick and easy way to do it? Thank you in advance. David You may be able to use R1C1 terminology, but you'll need to get someone else to help from here. "cyberdude" <honc...

Two profiles sharing one email account/pst?
I have a laptop, Win XP Pro & Office XP, that I use at work logging in as user/domain. At home I log in as user/laptop. The first time I used it at home & tried to check mail (mail was originally set up at work), it went through the motions to set up a new email account. Surely, I can share the same mail account or pst between profiles, but how? Thanks find the pst from work on your computer - if windows explorer, tools, options, view hidden files and folders is not set to show hidden files and folders, change it and then browse to C:\Documents and Settings\work_username\Local Setti...

Not allow entering repeated references in a column
Frank, If you don�t mind ... I will send it :) . As I don�t know your e-mail, I will give mine, which is ssouritinha@sapo.pt Just send your e-mail to my e-mail box and I will send the file. Thanks, Ritinh -- ritinh ----------------------------------------------------------------------- ritinha's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1566 View this thread: http://www.excelforum.com/showthread.php?threadid=27195 ...

Two mailboxes for one user account after mailbox moved to a new server
After moving a user's mailbox from a Exchange 2003 to a new Exchange 2003 server, Outlook has listed two mailboxes with same name in the "All Mail Folders"area. The Outlook profile only has the user mailbox and no other mailboxes added to it. The user can send and recieve email just fine. The mailboxes seem to be clones of each other. New mail show up in the mailboxes at the sametime. How can I get Outlook to only show one mailbox? Any help would be appreciated, thanks, ...

chart label reference based on the column number
In a worksheet with an embedded chart, I have a cell, S4, where I enter the number of the column I want to chart (these numbers are listed as labels in cells A2:R2). I can't figure out how to translate the number in S4 into the corresponding column letter. For example, if S4 contains "3", the chart title should be =$C$2. I think working with absolute references, R1C1, might be easier here, but somehow what I've tried, didn't work (the entire spreadsheet is based on relative addresses (A1). z.entropic in a cell (Z100) put =index(A2:G2,1,S4,1) Change G2 to whatev...

How many Server-side rules can you put on one mailbox?
I am using Exchange Server 2003 and want to setup 52 rules on one of the mailboxes, is this possible? the hardcoded limit on space for rules is 32K...i doubt you'll be able to get 52 rules on one mailbox... -- Susan Conkey [MVP] "jmareel" <jmareel@discussions.microsoft.com> wrote in message news:6ADDDCD7-948C-415F-B194-1944C9936BC2@microsoft.com... > I am using Exchange Server 2003 and want to setup 52 rules on one of the > mailboxes, is this possible? http://support.microsoft.com/default.aspx?scid=kb;en-us;147298 -- Bharat Suneja MVP - Exchange www.zenpris...

Project accounting info for vendor does not exist on version 9
We have 15 companies, of which only ONE company uses project accounting. User ID does NOT have access to the alternate POP window to show project information. Why are we receiving the error that the vendor does not have PA info setup, when we are not using PA in this company? How do we shut this off for window that is not showing PA? As well in the company that will be using PA, if the PA info does NOT show on the window, we are entering regular POs therefore we do NOT want any PA info for the vendor. ...

Move/Copy A Row Based on Formulas to a New Worksheet
I want to move several rows of sub-totals (averages within sub-groups) to a summary worksheet, but I get the Ref error. How can I copy sub-group averages to another worksheet? Thank you. high light and copy. select where you want it. edit>paste special>values. this will turn you formulas into hard numbers. you are getting the #Ref error because on the other sheet where you pasted the formulas, the formula no longer had the same references that they had on the other sheet. for example: =sum(a1:a10) in cell a11 you copy and paste on another sheet at cell a1. excell tries to compensat...

How do I get text to copy from one cell to another ?
Type = in the target cell. MouseClick the cell containing text. Pres Enter key -- Brian ----------------------------------------------------------------------- BrianB's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5 View this thread: http://www.excelforum.com/showthread.php?threadid=27034 ...

Bad certificate at Bank One?
I'm getting the error message when connecting to Bank One: "Money cannot establish secure communication with the Internet, banking, or brokerage server because the server's digital certificate is invalid. If the problem persists, contact your Financial Institution (OFXIE12038)." I wonder if the latest security updates to IE6 have anything to do with this ... -- Aloke ---- to reply by e-mail remove 123 and change invalid to com In microsoft.public.money, Aloke Prasad wrote: >I'm getting the error message when connecting to Bank One: > >"Money cannot e...

Incorporating detail of one sheet into another. #2
There are 2 excel worksheet. I would like to incorporate detail of sheet 2 into sheet1. e.g Sheet2 Contain the following details A B C D E F G Agent1 Agent2 Agent3 Agent4 Agent5 1 Apple Cycle 2 Mango Car 3 Banana Bus 4 Coconut Motor and so on in coloumn c , d , e,f and g. Now what I would like to do is If in sheet 1 If I select Agent 1 all the detail of sheet2 which has column as agent1 should appear exactly as it is in sheet2. And the same thing for Column B, c ...

Supress col/row increment with copy
Question with (hopefully) an easy answer.... When you copy a cell that uses a formula referring to another cell, for simple illustration say "=C4" it will increment based on where you paste it. So, it will increment up to "=C5" if you paste it in the next cell, or "=D4" if you paste below. How can I suppress that, so that when I paste the new cell receives "=C4" as well. (I know I can simply copy the text and paste that in, but I want to copy a whole row of formulas to be the same. I thought Paste>Special>Formulas would work, but it seems to ...

How can I have more than 64000 rows in one sheet?
I want to import an access database in one excel sheet, it requires 200.000 rows. Any suggestions? "blafblaf" <blafblaf@discussions.microsoft.com> wrote in message news:C3D0F75D-7F4E-4F76-95F3-72D7F3AE8D7A@microsoft.com... >I want to import an access database in one excel sheet, it requires 200.000 > rows. Any suggestions? Hi BlafBlaf, The current worksheet row limit of 65536 is not expected to be increased in the immediately foreseeable future. To import your database to Excel, it would therefore be necessary to pass 64k tranches to each of four worksheets. This...

Adding additional rows for data entry
I have a spreadsheet with five columns that I enter data to. I then have a blank row at the bottom of these columns. Below the blank row I have several formulas pertaining to each row. How do I add more data to the columns and have the formulas adjust for these new rows without highlighting rows and using the insert rows command to make room (empty rows) where I can then add the additional data to the columns. Is there a formula that would always leave one empty row even when new data is entered in the columns? Thanks Put the formulas at the top of the columns. You can even use a Freeze ...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

Only one line in Money invoice
I have been using Money 2007 Home & Business to create and print invoices. This program has run with no problems for some time. However, just recently, it will only allow one item to print on the invoice. I can enter more than one item, and the invoice entry will show the additional items, but when printed only one item will show up. I have tried backing up my Money file to a spare disk, deleting the program and the associated mny and mbf files from the computer, reinstalling the program and reloading the backup. The same fault immediately occurs. Strangely, I have now installed the sam...

one page academic calendar for 2004-05
Where can I find a one page academic calendar for 2004-05 with room for notes In Publisher, go to File > New > Print Publication > Calendar and create your own. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no warranties, and confers no rights. "shopperbev" <shopperbev@discussions.microsoft.com> wrote in message news:0940CD82-2A40-4932-982A-122ABFB41497@microsoft.com... > Where can I find a one page academic calendar for 2004-05 with room for > notes ...

Column comparing
I am trying to compare 2 columns of numbers so that I can identify and delete numbers no longer required. Can anyone help me find a formula for this please? Many thanks DT Check your earlier post. Dave T wrote: > > I am trying to compare 2 columns of numbers so that I can identify and > delete numbers no longer required. Can anyone help me find a formula for > this please? > > Many thanks > DT -- Dave Peterson ...

Hyper link one column to another
I would like to have my columns hyper link one another. For example: Click on B2 would take you to N2, and vice-versa. Click on C2 would take you to O2... and so on through column j linked to V. Is this possible without having to make the link for each cell? This is a timesheet template and the columns b though J are the hours and N through V are the text comments for those hours. When I copy the template to a new sheet, there will be no data in any column. I would like to be able to enter an amount or formula (=end-start) for time spent and then be able to click on that cell and hyperlin...

Reference: many to one
I have a form in which users enter information...I keep all controls locked until the user hits a command button to unlock particular bound controls. This works really well, but becomes cumbersome to code when new controls are added; e.g., adding the new control to the several event procedures attached to the form. Can anyone tell me how to refer to a group of controls once, e.g., give them a name; and then only have to refer to that name in the future? alex Alex I'm having trouble visualizing your situation. It sounds like you are describing a form that is being regularly modified....

Prevent Hidden Column data from being copied/pasted?
A student came up with a question that I haven't been able to figure out yet in a recent Excel class. They are hiding a column and protecting the worksheet in the correct manner. They want to allow some users to access and enter information in some cells. They do not want the users to be able to copy and paste the information from the hidden column. The question is how can this be prevented? For example, Column B is hidden. When they copy a range such as A1:C10 and paste it to another worksheet, they are getting the "hidden" data in Column B in B1:B10. Any suggestions wou...

Copying Publisher from One computer to another
I want to copy Publisher from my old computer to my new one, but if I can't seem to locate all the files I need to make it work on the new computer. What should I do? You can't, you have to install it from the CD. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Jasmin" <Jasmin@discussions.microsoft.com> wrote in message news:7EB558F1-525D-47BD-A54B-BC62C410D79E@microsoft.com... |I want to copy Publisher from my old computer to my new one, but if I can't | seem to locate all the files I need to make it work on the new computer. What | shoul...

combining columns all the way down
I am trying to combine two columns of information in excel but th concatenate function doesnt seem to work for it. It may just be m ineptitude in excel but I just cant seem to figure this out. I have 3 colums Column A__________Column B___________Column C tree_______________ .jpg dog________________.gif House______________.png But I cant seem to be able to make it so that column C has tree.jpg an dog.gif and house.png Is concatenate the wrong thing to use here? or am I making my formula incorrectly -- sparkrom ----------------------------------------------------------------------- sparkro...

Named ranges
Excel 2003 I have a total sheet that gets data from three different shift sheets (all in the same workbook) for a monthly report. I have named ranges on each shift sheet for each month's production data (S1M1, S1M2, S1M3, S2M1...) where S1M1 =A2:AC147, S1M2 = A148:AC329, etc. The old formula was:=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147). I'm stumped as to what the new formula would be - how would I direct Excel to column L of S1M1 to search for "BB", then add the contents of column T? The range changes each month, and I tho...