# Transpose Mail Merge

#### Budge

##### Board Regular
There is heaps of stuff about transposing on the board but I am more confused now.
My situation is:
There are 60 columns and 12000 rows.
The first 2 columns are conditional which are titled "Main Number" and "Sub Number"
The mail merge document will be populated from the row where there is a "Main Number" and the "Sub Number"=0.
So if "Main Number" Row 10 ="5" and "Sub Number" Row 10 ="0" then all the details in this row will be used.So far all OK.
BUT THE PROBLEM IS "Sub Number"(Column B) can have a value of 0,1 and 2.
So if ColumnA("Main Number") row 12="5" and Col B ("Sub")="1" then I need this row appended to Row 10.The data for type "0" stops at column 20,so the data for type"1" starts at column 21 and goes to col 40.
Same applies for data type 2 but goes to Col 60
If there is only 1 data type,ie 0 or 1 or 2 then there is no issue.This is the data sent.But if there is 0 and/or 1 and/or 2,then I'm stumped.
I have tried this formula but it does not work properly as once it finds a data type 2 it populates the other 2 cells when I copy my formula.
=IF(OR(B3=2),OFFSET(B3,0,30),IF(OR(B4=2),OFFSET(B4,0,30),IF(OR(B5=2),OFFSET(B5,0,30),"")))
I am using VBA at the moment but I need some assistance.Am i on the right track?
Code I have so far
Sub Copee()

Dim Currentcell, NextCell As Integer
Range("A2").Select
Currentcell = ActiveCell.Value
NextCell = ActiveCell.Offset(1, 0).Value

Currentcell = ActiveCell.Offset(1, 0).Select
Currentcell = ActiveCell.Value
NextCell = ActiveCell.Offset(1, 0).Value
If Currentcell = NextCell Then
Currentcell = Selection.EntireRow.Copy
End If
Loop
End Sub

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Budge.... what cell would be putting that formula in?

Hey Jim,
The formula for all data type "2" goes in columns 20 through to 40.Offset will increment by 1. Say cell b2=194,c2=0, b5=194 and c5=2,then I want the values from column 20 to column 40 on row 5 appended to the line where the data type is "0"
If Main number,"194" has a third data type,"3" then get all the values from columns 41-60 and append to row 2 which houses the destination ie in this case "194-0" This is to be appended to the end of data type 2.

I apologise for such a verbose question

Sincerely,
Budge

Not to desert you, but I am on Ohio time (EST) so if no one else jumps in (and they should feel free to do so) I will get back to you in the morning.

Thanks Jim
Budge

Budge,

It sounds like:
1. there may or may not be a sub = 0
2. there may or may not be a sub = 1
3. there may or may not be a sub = 2

So there could be any possible combination?

What if there is only a 1?

What if there is only a 1 & 2 (no 0)?

continued...

Below is a sample of what I believe you are looking for. I am only using 3 columns of data, but I understand there are 20.

row 1 shows the column number you referenced.

rows 3 -> 5 show how the data is.

row 7 shows how you want the data to be.

am I close?
Book2
ABCDEFGHI
1123212223414243
2MainSub
350x
451t
552c
6
750x51t52c
8
Sheet1

Hi Jim,
You've got it.Thats how the data needs to be.If there is only a "0'" or a "1" or a "2" then put this data into Row 7.If there is a combination then the 1 or the 2 line (or both) need to be appended to Row 7 (on the "0" line for the main number.
Anyway i have a solution and for the benefit of everybody who has followed my problem this far please see the code below.

Sub TransposeCR()

Dim nLastSupp, Currentcell, ChildNum, NextCell As Integer
Dim nMacroRow, nSheet3Row As Integer
Dim Backcell As Integer
Dim PasteLine As Integer
Dim Sheet3Range As String

Range("A2").Select

nLastSupp = -1000
Currentcell = ActiveCell.Value
LastCell = -1000

nMacroRow = 2
nSheet3Row = 2

If Currentcell <> LastCell Then
Currentcell = Selection.EntireRow.Copy
Worksheets("Macro").Activate
Range("a" & nMacroRow).Select
ActiveSheet.Paste
Worksheets("Sheet3").Activate
nMacroRow = nMacroRow + 1: DoEvents
Else
Backcell = nMacroRow - 1
ChildNum = ActiveCell.Offset(0, 1).Value
If ChildNum = 2 Then

Sheet3Range = "J" & nSheet3Row & ":AU" & nSheet3Row

Range(Sheet3Range).Select
Selection.Copy
Sheets("Macro").Select
Range("av" & Backcell).Select
ActiveSheet.Paste

Else
Backcell = nMacroRow - 1
ChildNum = ActiveCell.Offset(0, 1).Value
If ChildNum = 3 Then

Sheet3Range = "J" & nSheet3Row & ":AU" & nSheet3Row
Range(Sheet3Range).Select
Selection.Copy
Sheets("Macro").Select
Range("ac" & Backcell).Select
ActiveSheet.Paste
End If
End If
End If

Worksheets("Sheet3").Activate
Range("A" & nSheet3Row).Select
LastCell = ActiveCell.Value
Currentcell = ActiveCell.Offset(1, 0).Select
Currentcell = ActiveCell.Value
nSheet3Row = nSheet3Row + 1: DoEvents
Loop

End Sub

'Sheet 3 is the source data which has been sorted in ascending order.So you can evaluate the Main Numbers.If they equal then
you know there must be a child,either 2 or 3

'Sheets "Macro" is where the newly formatted data will live.

Hope this is of value.

Sincerely
Budge

Replies
6
Views
434
Replies
7
Views
568
Replies
7
Views
319
Replies
2
Views
252
Replies
1
Views
167

1,221,523
Messages
6,160,322
Members
451,637
Latest member
hvp2262

### 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.

### Which adblocker are you using?

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