Transpose Mail Merge

[Budge]

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

Do Until ActiveCell.Address = ("$A$1641")
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

 

[Jim North]

Budge.... what cell would be putting that formula in?

 

[Budge]

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

 

[Jim North]

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.

 

[Budge]

Thanks Jim
Budge

 

[Jim North]

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)?

 

[Jim North]

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
	A	B	C	D	E	F	G	H	I
1	1	2	3	21	22	23	41	42	43
2	Main	Sub
3	5	0	x
4	5	1	t
5	5	2	c
6
7	5	0	x	5	1	t	5	2	c
8
Sheet1

 

[Budge]

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
Do Until ActiveCell.Address = ("$A$1641")

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.

Thanks for your help.

Sincerely
Budge