Solved

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

Posted on 2006-10-19
4
735 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

740 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