Loop Macro

[Guest]

I have a sheet with a column of random question numbers in A and answers (all different) in B. Many of the question numbers are repeated . The task at hand is to write a macro to create a new sheet with each unique question number in a single row and all the answers for that question concatenated in column B.

Example:

Orig. Sheet

A B
QNo Question
1 AAA
1 BBB
1 CCC
3 ABC
3 DDD
6 KKK


New Sheet:

QNo Question
1 AAA;BBB;CCC
3 ABC;DDD
6 KKK

Thanks for your help

 

[Frank Kabel]

Hi
maybe also a non macro solution (only using some free add-ins) would be
of interest for your. Lets assume your data is on a sheet called 'Data'
then enter the following formulas on your new sheet:
A1:
='data'!A1

A2: Enter the array formula (with CTRL+SHIFT+ENTER):
=IF(MIN(IF((COUNTIF($A$1:$A1,Data!$A$1:$A$100)=0)*(Data!$A$1:$A$100<>""
),ROW(INDIRECT("1:100"))))<>0,INDEX(Data!$A$1:$A$10,MIN(IF((COUNTIF($A$
1:$A1,Data!$A$1:$A$100)=0)*(Data!$A$1:$A$100<>""),ROW(INDIRECT("1:100")
)))),"")

copy this formula down as far as needed.
Now also download the following add-ins / macro code:
- download Alan Beban's array functions:
http://home.pacbell.net/beban
- download the free add-in Morefunc.xll
http://longre.free.fr/english/

After this enter the following formulas:
B1:
=MCONCAT(VLOOKUPS(A1,'Data'!$A$1:$B$100,2),"; ")
and copy this formula down

 

[Dave Peterson]

If you want a macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim newWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Set CurWks = Worksheets("sheet1")

CurWks.Copy _
after:=CurWks
Set newWks = ActiveSheet

With newWks
'headers in row 1
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & ";" _
& .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow

.UsedRange.Columns.AutoFit
End With

End Sub

 

[Guest]

I need a Macro I can call from the original sheet (without add-ins). Thanks.

 

[Dave Peterson]

Are you replying to Frank's suggestion or mine?

If mine, just put that macro in your workbook.

 

[Guest]

Dave: I can't find your suggestion. Could you repost it. Thanks much.

Dean

 

[Dave Peterson]

If you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

It's in this thread:
http://groups.google.com/[email protected]

 

[Guest]

Got it. It's just what I needed. Thanks a lot.

If you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

It's in this thread:
http://groups.google.com/[email protected]