Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-10-19
4
Medium Priority
?
750 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 2000 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

572 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