Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2008

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
SNK - 27 Mar 2008 08:30 GMT
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 - 27 Mar 2008 13:52 GMT
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

> 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
[quoted text clipped - 7 lines]
> are 2) tables. Two graphs will be generated based on these two tables. pl.
> suggest a macro for this.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.