Solved

EXCEL GROUP ITEMS IN COLUMN B BY QUALIFYING DATA IN COLUMN A - HELP!!!

Posted on 2006-10-19
4
733 Views
Last Modified: 2011-09-20
Experts,

Whether by macro or formula, I need the following:

I have a spreadsheet that currently has 2 columns.  Column A has numeric values sorted that can are not necessarily unique, thus you could see the below:

A
--
1
1
1
2
2
3
4
4
4
5
6
7
7
8
8
8

Column B has values that are unique, there are no duplicates and coorespond to the values in column A.  Thus column B could look like the below:

B
--
24
13
65
85
29
6
23
11
17
66
98
44
79
31
20
2

So together, we have this:

A              B
--             --
1      24
1      13
1      65
2      85
2      29
3      6
4      23
4      11
4      17
5      66
6      98
7      44
7      79
8      31
8      20
8      2

What I want instead of the above, is this:

1      24, 13, 65
2      85, 29
3      6
4      23, 11, 17
5      66
6      98
7      44, 79
8      31, 20, 2

I don't anticipate having more than 10000 rows (ever) and right now have under 1000.  Thank you very much...!!!

0
Comment
Question by:stevenjoe
  • 2
  • 2
4 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 500 total points
ID: 17767887

Try this sub, assuming that the data starts from row 1

Sub unitendc()
Cells(1, 2) = "'" & Cells(1, 2)
For i = 2 To UsedRange.Rows.Count
For j = 1 To i - 1

If Cells(j, 1) = "" Then
Cells(j, 1) = Cells(i, 1)
Cells(j, 2) = "'" & Cells(i, 2)
Cells(i, 1).ClearContents
Cells(i, 2).ClearContents
Exit For
End If

If Cells(i, 1) = Cells(j, 1) Then
Cells(j, 2) = Trim(Cells(j, 2)) + ", " & Trim(Cells(i, 2))
Cells(i, 1).ClearContents
Cells(i, 2).ClearContents
End If

Next j
Next i

End Sub

Saqib
0
 

Author Comment

by:stevenjoe
ID: 17767949
I got a runtime error, object required and then it highlighted this in yellow:

For i = 2 To UsedRange.Rows.Count

???
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 17768026
Try


For i = 2 To sheet1.UsedRange.Rows.Count


or replace sheet1 with the name of your worksheet
0
 

Author Comment

by:stevenjoe
ID: 17768066
Pure genius...!!!
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question