Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

719 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