How do I split one table in multiple tables base on condition?

[SNK]

I have set of data in two columns. This data should get segregated in
different tables which will further be used for plotting graphs (depending on
the number of tables the data is segregated/ sorted in. The data is like
this: 1-2, 2-3, 3-4, 4-5, 4-6,6-7. Here note that the first number is in one
column and second in another. data in other columnns is associated with set
of these ftwo columns. If u observe the data, the chain breaks at node 4
where there is branching from 4 to 5 and 4 to 6. in another branch the chain
continues from 6-7. Now I need to end one chain from 1---4-5 and another
chain from 1----4-6-7. This should be generated in two (as number of branches
are 2) tables. Two graphs will be generated based on these two tables. pl.
suggest a macro for this.

 

[Joel]

The way to do this is to copy each range of data to a new worksheet. Add a
column to the Master worksheet indicating which rows were copied. Also sort
the data by the first number to simplify the coding. continue looping
through the code until all rows ae copied.

I did not test the code below, but it basically does the job. the code will
work with multiple ranges of data, not just two groups. I used the first
column as the first number and the second column as the 2nd number. these
are respectively B and C after I inserted a new column.




Sub split_table()

'add column to use as marker to indicate which rows were used
With Sheets("Master")
.Columns("A:A").Insert
'sort by first column of table now 2nd column
.Rows("1:16").Sort _
Key1:=.Range("B1"), _
Order1:=xlAscending, _
Header:=xlGuess
Do
RowCount = 1
First = True
Do While .Range("B" & RowCount) <> ""
'check if row was used
If .Range("A" & RowCount) = "" Then
If First = True Then
'create new worksheet
Sheets.Add after:=Sheets(Sheets.Count)
Set NewSheet = ActiveSheet
NewRowCount = 1
FindNum = .Range("C" & RowCount)
First = False
.Range("A" & RowCount) = "Copied"
.Rows(RowCount).Copy _
Destination:=NewSheet.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
Else
If .Range("B" & RowCount) = FindNum Then
.Range("A" & RowCount) = "Copied"
.Rows(RowCount).Copy _
Destination:=NewSheet.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
FindNum = .Range("C" & RowCount)
End If
End If
End If
RowCount = RowCount + 1
Loop
Loop While First = False
End With
End Sub