Loop Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
Are you replying to Frank's suggestion or mine?

If mine, just put that macro in your workbook.
 
Got it. It's just what I needed. Thanks a lot.

Dave Peterson said:
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]
Click to expand...
 
Back
Top