Combining rows of data

bydganwil

New Member
I have a set of data that looks like this:

 Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Target 1 Y Y Y Y Target 1 Y Y Y Target 1 Y Y Y Target 2 Y Y Y Target 2 Y Y Y Target 2 Y Y Y Y Target 3 Y Y Y Target 3 Y Y Y Y Target 3 Y Y Y

<TBODY>
</TBODY>

And I need it to look like this please:

 Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Target 1 Y Y Y Y Y Y Y Y Y Y Target 2 Y Y Y Y Y Y Y Y Y Y Target 3 Y Y Y Y Y Y Y Y Y Y

<TBODY>
</TBODY>

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Active Member
Hi,
let Your Data be in A1:K10, then try to run a following code:
Code:
``````Sub abstract()
Dim a&, i&, j&, k&

a = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & a).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"M1"), Unique:=True

For i = 2 To Cells(Rows.Count, "M").End(xlUp).Row
For j = 2 To a
If Cells(j, 1).Value = Cells(i, "M").Value Then
For k = 2 To 11
If Cells(j, k).Value <> "" Then
Cells(i, k + 12).Value = Cells(j, k).Value
End If
Next k
End If
Next j
Next i
End Sub``````
Best regards.

Last edited:

patel45

Well-known Member
Code:
``````Sub a()
r = 3
numcol = 11
Do While Cells(r, 1) <> ""
If Cells(r, 1) = Cells(r - 1, 1) Then
For c = 2 To numcol
If Cells(r - 1, c) = "" Then Cells(r - 1, c) = Cells(r, c)
Next
Rows(r).Delete
Else
r = r + 1
End If

Loop
End Su``````
b

kevatarvind

Well-known Member
let Your Data be in A1:K10, then try to run a following code:

Code:
``````Sub Test()
Dim rng As Range
Set rng = Range("B2:K10")
For Each Cell In rng
If Cell <> "" Then
Cell.Cut
Cells(WorksheetFunction.Match(Cells(Cell.Row, 1), Range("A1:A10"), 0), Cell.Column).Select
ActiveSheet.Paste
End If
Next
Columns("A:A").Select
Range("A1").Select
End Sub``````

Last edited:

bydganwil

New Member
Hi there,

The code you provided is not doing anything that I can see. When I run the code it gets to the 1st For statement and then the sub ends. In addition the xlfiltercopy to range M1 is not writing any data in the worksheet (assuming I understand what your code is trying to do?)

Thanks for your time really appreciated!!!!

bydganwil

New Member
Patel45 - That is looking like the ticket!!

Many thanks everyone

kevatarvind

Well-known Member
let Your Data be in A1:K10, then try to run a following code:

Code:
``````Sub Test()
Dim rng As Range
Set rng = Range("B2:K10")
For Each Cell In rng
If Cell <> "" Then
Cell.Cut
Cells(WorksheetFunction.Match(Cells(Cell.Row, 1), Range("A1:A10"), 0), Cell.Column).Select
ActiveSheet.Paste
End If
Next
Columns("A:A").Select
Range("A1").Select
End Sub``````

Active Member
Try to write in A1 the word "Target" and then try to run my Code... best regards.

Hi there,

The code you provided is not doing anything that I can see. When I run the code it gets to the 1st For statement and then the sub ends. In addition the xlfiltercopy to range M1 is not writing any data in the worksheet (assuming I understand what your code is trying to do?)

Thanks for your time really appreciated!!!!

Replies
3
Views
159
Replies
8
Views
174
Replies
19
Views
249
Replies
1
Views
466
Replies
3
Views
219

1,195,651
Messages
6,010,930
Members
441,573
Latest member
Goronvir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back