Grouping in one row

Hi

I have following problem, i have 5 columns of data, the first column
is ID number. There can be many rows with the same ID. What I want to
achieve is to group values from one ID in one row.

For example I have:

a,cat,excel,12, ,4
a,2,4, ,fire
a, ,fire,543,12
b,qwerty,six,alpha,3
b,34,enter, ,3
c,with,sober,lax,23
c,2, ,4,3

and want to get:

a,cat,excel,12, ,4,2,4, ,fire, ,fire,543,12
b,qwerty,six,alpha,3,34,enter, 3
c,with,sober,lax,23,2, 4,3

Can this be done in excel or vba?
0
3/3/2012 10:04:41 PM
excel 39879 articles. 2 followers. Follow

4 Replies
624 Views

Similar Articles

[PageSpeed] 3

On Mar 3, 5:04=A0pm, Johan Ibrahim <johanibrah...@gmail.com> wrote:
> Hi
>
> I have following problem, i have 5 columns of data, the first column
> is ID number. There can be many rows with the same ID. What I want to
> achieve is to group values from one ID in one row.
>
> For example I have:
>
> a,cat,excel,12, ,4
> a,2,4, ,fire
> a, ,fire,543,12
> b,qwerty,six,alpha,3
> b,34,enter, ,3
> c,with,sober,lax,23
> c,2, ,4,3
>
> and want to get:
>
> a,cat,excel,12, ,4,2,4, ,fire, ,fire,543,12
> b,qwerty,six,alpha,3,34,enter, 3
> c,with,sober,lax,23,2, 4,3
>
> Can this be done in excel or vba?

It could certainly be done in VBA. How difficult it would be
depends on if the sorting in your example is dependable.
That is, is the ID column always sorted? It's a fairly
straightforward thing then. You just move the cells up
when the ID is the same as the previous row. This would
seem to produce more  than five columns. Is that OK?
And it seems to not bother about duplicates. Is that OK?
Socks
0
puppet_sock (108)
3/5/2012 6:07:55 PM
On Saturday, March 3, 2012 4:04:41 PM UTC-6, Johan Ibrahim wrote:
> Hi
> 
> I have following problem, i have 5 columns of data, the first column
> is ID number. There can be many rows with the same ID. What I want to
> achieve is to group values from one ID in one row.
> 
> For example I have:
> 
> a,cat,excel,12, ,4
> a,2,4, ,fire
> a, ,fire,543,12
> b,qwerty,six,alpha,3
> b,34,enter, ,3
> c,with,sober,lax,23
> c,2, ,4,3
> 
> and want to get:
> 
> a,cat,excel,12, ,4,2,4, ,fire, ,fire,543,12
> b,qwerty,six,alpha,3,34,enter, 3
> c,with,sober,lax,23,2, 4,3
> 
> Can this be done in excel or vba?

Sub columnstorowsSAS()
Dim i As Long
Dim slc As Long
Dim dlc As Long
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i + 1, 1) = Cells(i, 1) Then
slc = Cells(i + 1, Columns.Count).End(xlToLeft).Column
'MsgBox slc
dlc = Cells(i, Columns.Count).End(xlToLeft).Column + 1
'MsgBox dlc
Cells(i + 1, 1).Resize(, slc).Copy Cells(i, dlc)
Rows(i + 1).Delete
End If
Next i
End Sub
0
dguillett11 (103)
3/5/2012 8:18:33 PM
On Sat, 3 Mar 2012 14:04:41 -0800 (PST), Johan Ibrahim <johanibrahim3@gmail.com> wrote:

>Hi
>
>I have following problem, i have 5 columns of data, the first column
>is ID number. There can be many rows with the same ID. What I want to
>achieve is to group values from one ID in one row.
>
>For example I have:
>
>a,cat,excel,12, ,4
>a,2,4, ,fire
>a, ,fire,543,12
>b,qwerty,six,alpha,3
>b,34,enter, ,3
>c,with,sober,lax,23
>c,2, ,4,3
>
>and want to get:
>
>a,cat,excel,12, ,4,2,4, ,fire, ,fire,543,12
>b,qwerty,six,alpha,3,34,enter, 3
>c,with,sober,lax,23,2, 4,3
>
>Can this be done in excel or vba?

Relatively easy to do with a macro.  You do not write where you want the results, so in this example I placed them adjacent to (starting in column H) your original data.  But if it works OK, that can easily be changed.  Also, this macro, does not expect that the data will be sorted.  It does not sort the results, either, but that could easily be added.

Finally, it assumes your data starts in A1, and, like your example, has no column labels; again, an easy change to make if that is not the case.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

Once satsfied, UNcommenting the Application.Screenupdating = False line will speed up execution.

======================================
Option Explicit
Sub CombineRows()
    Dim rSrc As Range, rDest As Range, c As Range
    Dim vSrc As Variant, vRes As Variant
    Dim v1 As Variant, v2() As String
    Dim collSrc As Collection
    Dim i As Long, j As Long
    Dim sTemp As String
    Dim sFirstAddress As String
 
   
'Application.ScreenUpdating = False

Set rSrc = ActiveSheet.Range("a1").CurrentRegion
Set rDest = rSrc(1, rSrc.Columns.Count + 2)
    rDest.CurrentRegion.Clear

'get list of unique ID's
Set collSrc = New Collection
On Error Resume Next
    For Each c In rSrc.Columns(1).Cells
        collSrc.Add Item:=c.Text, Key:=CStr(c.Text)
    Next c
On Error GoTo 0

'Build Results array
ReDim vRes(1 To collSrc.Count, 0 To 1)
For i = 1 To collSrc.Count
    vRes(i, 0) = collSrc(i)
    Set c = rSrc.Find(what:=vRes(i, 0), _
                after:=rSrc(rSrc.Rows.Count, 1), _
                LookIn:=xlValues, lookat:=xlWhole, _
                searchdirection:=xlNext, MatchCase:=True)
        sFirstAddress = c.Address
        Do
            v1 = Range(c.Offset(columnoffset:=1), _
                c(columnindex:=Columns.Count).End(xlToLeft))
            ReDim v2(1 To UBound(v1, 2))
            For j = LBound(v2) To UBound(v2)
                v2(j) = v1(1, j)
            Next j
            vRes(i, 1) = vRes(i, 1) & "," & Join(v2, ",")
            
            Set c = rSrc.FindNext(after:=c)
        Loop While c.Address <> sFirstAddress
    vRes(i, 1) = Mid(vRes(i, 1), 2)

Next i

Set rDest = rDest.Resize(rowsize:=UBound(vRes, 1), columnsize:=2)
rDest = vRes
rDest.Columns(2).TextToColumns comma:=True, Tab:=False, semicolon:=False, _
                    Space:=False, other:=False
                    

Application.ScreenUpdating = True
End Sub
================================
0
ron6368 (329)
3/6/2012 12:12:08 PM
On Tue, 06 Mar 2012 07:12:08 -0500, Ron Rosenfeld <ron@nospam.net> wrote:

>On Sat, 3 Mar 2012 14:04:41 -0800 (PST), Johan Ibrahim <johanibrahim3@gmail.com> wrote:
>
>>Hi
>>
>>I have following problem, i have 5 columns of data, the first column
>>is ID number. There can be many rows with the same ID. What I want to
>>achieve is to group values from one ID in one row.
>>
>>For example I have:
>>
>>a,cat,excel,12, ,4
>>a,2,4, ,fire
>>a, ,fire,543,12
>>b,qwerty,six,alpha,3
>>b,34,enter, ,3
>>c,with,sober,lax,23
>>c,2, ,4,3
>>
>>and want to get:
>>
>>a,cat,excel,12, ,4,2,4, ,fire, ,fire,543,12
>>b,qwerty,six,alpha,3,34,enter, 3
>>c,with,sober,lax,23,2, 4,3
>>
>>Can this be done in excel or vba?
>
>Relatively easy to do with a macro.  You do not write where you want the results, so in this example I placed them adjacent to (starting in column H) your original data.  But if it works OK, that can easily be changed.  Also, this macro, does not expect that the data will be sorted.  It does not sort the results, either, but that could easily be added.
>
>Finally, it assumes your data starts in A1, and, like your example, has no column labels; again, an easy change to make if that is not the case.
>
>To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
>Ensure your project is highlighted in the Project Explorer window.
>Then, from the top menu, select Insert/Module and
>paste the code below into the window that opens.
>
>To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.
>
>Once satsfied, UNcommenting the Application.Screenupdating = False line will speed up execution.
>

Minor change, to limit the Find method to just column 1 of the data.  I had that initially, but removed it for debugging, then forgot to add it back in.

================================
Option Explicit
Sub CombineRows()
    Dim rSrc As Range, rDest As Range, c As Range
    Dim vSrc As Variant, vRes As Variant
    Dim v1 As Variant, v2() As String
    Dim collSrc As Collection
    Dim i As Long, j As Long
    Dim sTemp As String
    Dim sFirstAddress As String
 
'UNcomment next line to speed up macro   
'Application.ScreenUpdating = False

Set rSrc = ActiveSheet.Range("a1").CurrentRegion
Set rDest = rSrc(1, rSrc.Columns.Count + 2)
    rDest.CurrentRegion.Clear

'get list of unique ID's
Set collSrc = New Collection
On Error Resume Next
    For Each c In rSrc.Columns(1).Cells
        collSrc.Add Item:=c.Text, Key:=CStr(c.Text)
    Next c
On Error GoTo 0

'Build Results array
ReDim vRes(1 To collSrc.Count, 0 To 1)

With rSrc.Columns(1)
For i = 1 To collSrc.Count
    vRes(i, 0) = collSrc(i)
    Set c = .Find(what:=vRes(i, 0), _
                after:=rSrc(rSrc.Rows.Count, 1), _
                LookIn:=xlValues, lookat:=xlWhole, _
                searchdirection:=xlNext, MatchCase:=True)
        sFirstAddress = c.Address
        Do
            v1 = Range(c.Offset(columnoffset:=1), _
                c(columnindex:=Columns.Count).End(xlToLeft))
            ReDim v2(1 To UBound(v1, 2))
            For j = LBound(v2) To UBound(v2)
                v2(j) = v1(1, j)
            Next j
            vRes(i, 1) = vRes(i, 1) & "," & Join(v2, ",")
            
            Set c = .FindNext(after:=c)

        Loop While c.Address <> sFirstAddress
    vRes(i, 1) = Mid(vRes(i, 1), 2)
Next i
End With

Set rDest = rDest.Resize(rowsize:=UBound(vRes, 1), columnsize:=2)
rDest = vRes
rDest.Columns(2).TextToColumns comma:=True, Tab:=False, semicolon:=False, _
                    Space:=False, other:=False
                    

Application.ScreenUpdating = True
End Sub
=====================================
0
ron6368 (329)
3/6/2012 1:01:29 PM
Reply:

Similar Artilces:

how do I get more than one sub-total in pivot tables?
I am using pivot tables to show summary HR recruiting data. The data columns are nested at three levels - priority(a, b or c), type(add/replace), number of reqs and number of positions. The pivot table automatically gives me sub-totals within the priority so I get number of reqs that are adds of priority A for example. I also automatically get a total of number of reqs and total of number of positions. What I'm trying to get is the sub-total of number of reqs that are adds regardless of priority. Move Type to the column area, and you'll get columns with totals for Add and...

How to increase the row hight in Excel
My row height will only go to 175.5 then when I increase it manually the data just goes down leaving the top with no data. The more I manually increase the cell height the more space I have on top. Comments don't work because if I enter any data one the first cell as an example then every comment is throw out of whack therefore I have chosen to take all the comments and put them in their own cell where I know they will stay. Maybe try re-doing the alignment of the cell..........Right-click > AlignmentTab > change the Vertical: window to "TOP" > ok Vaya con Dios,...

Mail to more than one address
When I send email (invoices)through my accounting program to more than one recipient, I get the following; Message could not be sent because one of the recepients was rejected by the server. Server response 5015.54 syntax error in parameter scanning. Does anyone know what this means? one of the addresses you are trying to send to is bad - examine the email addresses for errors. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide)...

Compare two file/colunms, hide row not does not equal list.
Excel 2000 -have two files. File "A" has 1 through 4155 records (rows with three columns) in numerical order. File "B", has a list of almost 200 rows/numbers (one column) from file "A". How can I hide the rows in file "A", not are listed in file "B"? Thanks, Jerry Not sure whether you are still monitoring this post, Jerry. Anyway, here's some thoughts ventured .. Conceptually, you should be able to achieve this via setting up a helper col and then autofiltering on the helper. Let's start by simplifying the scenario by having bo...

Recipient policy and Security Groups
Hi, I have created a recipient policy to update the e-mail addresses for members of a security group that has an e-mail address. The default policy is applying instead of the custom policy. I have ensured that the custom policy is a higher priority than the default. The environment is E2K3 W/SP1 on W2K3 server standard. Thanks for any replies. Warren ...

One paystub, two paychecks
I work for the U.S. Navy and therefore receive my paychecks from Defense Finance and Accounting Service (DFAS). The way DFAS pays me is like this: -On the first day of the month, DFAS projects how much I'll earn that month in base pay, how much I'll pay in taxes, additional deductions/allotments, and then comes up with my Net Pay for the month. Pretty straightforward. -On the 15th of the month, DFAS pays me -half- of my projected Net Pay for that month. -On the last day of the month, DFAS pays me the other half. At that point I get a paystub which reflects my income, taxes, ...

Adjust Row Height for Text
I am working on a worksheet, and I have a row that contains text that I want to restrain in length so the text automatically wraps around and starts a new line within the row. I've tried "help" in Encel, but I can't figure how to do this. I want a wrap around in the row, because I don't want the text to exceed the limits of the paper when the worksheet is printed out. Any help would be appreciated. Pedro -- To contact me directly by email, please remove the words "NO" and "SPAM" from the reply address above. Alt click on the cell you want...

When I add a row to excel how can I stop formulae changing
I've done this before, but can't remember how. I want to add a row at the top of a sheet, but without formulae with a range eg =sum(a1:a5) changing to =sum(a2:a6). $ isn't the answer =SUM(INDIRECT("A1:A5")) HTH Kostis Vezerides "vezerid" wrote: > =SUM(INDIRECT("A1:A5")) > > HTH > Kostis Vezerides > Thanks for that. How about if I after inserting a row A1:A5 changes to A1:A6 TIA Dave ...

bar chart prints 1 unwanted horizontal row of dots & tick marks?
Office Excel 2007. Anyone encounter this? The sheet I'm printing is 19 columns wide by 69 rows high with portrait orientation. Bar chart is set between row 25 and 47 horizontally. The x-axis is increasing months. 2 vertical bars are set between the major gridlines. It so happens that the chart vertical gridlines are very close to lining up with the spreadsheet cells above/below the chart. Print preview shows nothing unusual, but when printed, 1 row right throught the middle of the chart has 1 to 2 dots and a character high tick mark appear to the right of each right most(second) bar...

When I Select More Than One Tab
Is there a way for Excel to tell me when I have more than one tab selected? Other than me remembering to look at the top and see [Group] in the file name. Any ideas are welcome! (B^>)-]=[ The grouped sheets' tabs will be white. Gord Dibben MS Excel MVP On Wed, 28 Apr 2010 15:20:51 -0400, "WSR" <wsr-203@hotmail.com> wrote: >Is there a way for Excel to tell me when I have more than one tab selected? > >Other than me remembering to look at the top and see [Group] in the file >name. > >Any ideas are welcome! > >(B...

Transfer data from one sheet to another
Did I stump you guys? This is a hard one! Here is the senario: Worksheet one has a cell with a drop down menu with the numbers 1-10 in it. Below the drop down menu are two columns labled 'account number' and 'method' each with many fields available for info below them. Worksheet 2 is a database. It has many columns labled 'account number', ' method', 'date', 'cycle' etc. The cycle field is what contains the numbers 1-10 of the previous worksheet. I need a way of making it so that when I choose 1-10 from the dropdown menu on the fir...

Need a simple macro that will import any one of a number of text files in a folder
I'm having problems coming up with a macro that will allow me to import a text file of choice from my default file location that I have set up with Excel which contains several text files. I want the macro to open a "Open" file dialog window where I can then choose the text file of choice. I also need it to open the file as delimited, starting at row 23 and with the tab selected as the only delimiter. Please help... thanks, steve Steve I have been using the following for several years to obtain a spreadsheet from a .csv file downloaded from a bank. You will need to add the...

How do I sum dollars in one column based on dates in another?
How can I sum the total numbers contained in one column based on dates in another column? Example: I need the total of 5 units sold int the month of January 2006. I have the sale ammounts in one column and the closing dates in another. What forumla would I use for this? Thanks in advance for any help. =SUMPRODUCT(--(MONTH(B2:B50)=1),--(YEAR(B2:B50)=2006),A2:A50) for January change 1 in the month part to a different month -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Alan" <Alan@discus...

Create individual worksheets for selected rows in a table
Hi guys, I hope somebody may be able to help. I want to be able to automate the following procedure Create individual worksheets for selected rows in a table. Many thanks - Batman2002 Batman, If you really want the entirerow, then use this - if you just want the selection, then remove the .EntireRow of the last line: Sub CopySelectioToNewWorksheet() Dim mySheet1 As Worksheet Dim mySheet2 As Worksheet Set mySheet1 = ActiveSheet Set mySheet2 = Sheets.Add(Type:="Worksheet") mySheet1.Activate Selection.EntireRow.Copy mySheet2.Range("A1") End Sub HTH, Bernie "...

delete rows containing blank/missing values
I have very large database( almost 500,000 ) that contains road characteristic readings in two columns taken by a highly sophisticated instrument . I would like to remove the rows that the instrument missed to take the reading. Could some one help me in doing this. The normal technique would be to construct a SELECT query that returns only the rows you want. What distinguishes these rows from the others? On 10 Mar 2007 21:47:50 -0800, prasanna.kusam@gmail.com wrote: >I have very large database( almost 500,000 ) that contains road >characteristic readings in two columns taken by a hi...

Finding the last row in a spreadsheet
Hi all. I'm a relative newbie to using Excel and am stumped. I'm trying to find the last (non-zero) value in a column. The cells all have a function in them presently like =IF(C8>0,SUM(B8:C8)," ") presently so that the cell truly is not empty. I've tried using =LOOKUP(2,1/(D2:D350>0),D2:D350) but it displays nothing since all the cells have a value (the function above). How do I find the last cell having a numeric value (which is what the first function provides)? Any help is greatly appreciated. It's because you don't use empty as opposed to space &...

User Mailbox that more than one person sends e-mail from
Do to a SARBOX requirement we were told to set all user passwords expire. The issue is we have some users that we set up where more than one person sends e-mail from it. We set the mailbox up in outlook for the people that need to send mail as that user. When those user's password expires the person trying to send - gets an error and is not allowed to send. Is there any way to get around this issue? Thanks Jennifer Jennifer: For your shared mailbox, I would disable that account. Then, create a security group that contains as members everyone that should have permissions t...

Deleting Rows if..
I'm trying to get visual basic to delete all the rows that have a certain value (i.e. 1) in a specific column. I've seen this done with a loop, but I'm not sure how it all works. Any insight would be great Thanks Dennis Try the example on this page Dennis http://www.rondebruin.nl/delete.htm Post back if you need help -- Regards Ron de Bruin http://www.rondebruin.nl "dennis" <dennis.chou@ibtco.com> wrote in message news:010601c423fb$764c4590$a301280a@phx.gbl... > I'm trying to get visual basic to delete all the rows that > have a certain value...

Auto-select combo box entry when only one row
I have 2 combo boxes the second is dependent on the first. When the first is clicked, I requery the second in the OnClick event. When the requeried combo box has only one row, I'd like this to be auto selected. How can I do this? Thanks. "mscertified" <rupert@tigerlily.com> wrote in message news:3AACB546-54A0-4BC2-8AFA-03AEB37F001E@microsoft.com... >I have 2 combo boxes the second is dependent on the first. When the first >is > clicked, I requery the second in the OnClick event. When the requeried > combo > box has only one row, I'd like this to be...

Conditional copy cells from one excel file into current
Hello All, Does a macro (or function or ??) exist to conditionally copy value from a stored spreadsheet into the current (open) spreadsheet? Here's the scenario: Stored spreadsheet is two columnsof text (~200 rows) : Think explici question in column one, then the correct answer in column two of th same row. Then my current (open) spreadsheet has SOME of the same questions i column one. I would like to invoke a [macro?] that, in pseudo code, does somethin similar to the following: for each row (question in column 1) of the current spreadsheet Search in column 1 of *stored* spreadshee...

I want to show chart data in order of bars, not row names
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am trying to show a chart that has about 20 values, and I have sorted the source data so that I have the highest value items at the top and the lowest value items at the bottom. For example, if rows were Apple, Banana, Guava, Orange, and Yucca, I have sorted the data in order of the greatest value items (20 Guavas, 10 Oranges, 5 yucca, 2 bananas, and 1 apple). Yet the chart (a bar chart) only seems to let me display in either ascending or descending value by food name (either Apple to Yucca or Yucca to Apple...

Multiple Calendars but Shared One is Selected
When a shared Calendar or multiple shared calendars are displayed, when swithcing between "Mail" and "Calendar" the other calendar or one of the others is selected when pressing "Calendar" and viewing those calendars. On every other users' machine I have tested the users' calendar is always selected no matter how many shared calendars are in the calendar view. Running Office 2007 on Windows XP - all fully updated with exchange server 2003. I already refreshed roaming and local profile and re-installed office products. Both steps had no ef...

how do i delete the extra empty rows and columns that i dont need.
I want to limit the size of the worksheet to include only the rows & columns that I need. Thanks in advance Select the first blank row, hit Shift+Ctrl+Down arrow. Edit-->Delete. Select the first blank column, hit Shift+Ctrl+Right arrow. Edit-->Delete Save. ******************* ~Anne Troy www.piersontech.com www.mrexcel.com "lindaY" <lindaY@discussions.microsoft.com> wrote in message news:1BA1DB0B-002C-4C0C-A241-5BAC704869B6@microsoft.com... > I want to limit the size of the worksheet to include only the rows & columns > that I need. > > Thanks in...

Can I make column width and row height equal (a grid) in Excel?
I'd like to use a worksheet like a piece of graph paper. Is there any simple way to set the column width and the row height to form perfect squares in a worksheet? Buzz MS Office Excel Template for graph paper........ http://office.microsoft.com/en-us/templates/TC010183751033.aspx If you want to "roll your own"....... Assuming 96 pixels per inch you could approximate but at different resolutions your approximation can be off. If you want to use VBA to set height and width in mm which you then convert to inches at 25.4 mm per inch.......... Ole Erlandson has code for set...

** One Million FR.EE Visitors ** #4
How To Get 1 Million Visitors On Your Web Site Without Paying A Dime In advertising ! Are you frustrated by the lack of traffic coming to your site? If I would tell you that after months of research, I just got my hands on the most hidden secrets... very sneaky tricks ! Click here : http://freeadguru.com/cgi-bin/i.pl?c=a&i=33977 Hurry, before the page gets banned! Regards, Mark van Iterson --- MAF Anti-Spam ID: 20060831105137X1q7YbV9 ...