Move records to different sheet based on criteria

O

oxygn

New Member
Joined
May 24, 2010
Messages
25
Hi guys,

I have a sheet with following data:
[TABLE="width: 354"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 354"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Item name[/TD]
[TD]Sheet[/TD]
[/TR]
[TR]
[TD]Subject 1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Subject 2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Subject 3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Subject 4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Subject 5[/TD]
[TD]1,2,3[/TD]
[/TR]
[TR]
[TD]Subject 6[/TD]
[TD]1,4[/TD]
[/TR]
[TR]
[TD]Subject 7[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Subject 8[/TD]
[TD]1,3,4[/TD]
[/TR]
[TR]
[TD]Subject 9[/TD]
[TD]2,3[/TD]
[/TR]
[TR]
[TD]Subject 10[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Subject 11[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Subject 12[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Subject 13[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Subject 14[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column A lists all the courses, column b lists the sheets a course needs to go to.

The output sheets are named Course 1.... 4

So a sample output for Course 1 (sheet) will look like:
[TABLE="width: 849"]
<colgroup><col><col span="12"></colgroup><tbody>[TR]
[TD]Course
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Subject 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Subject 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Subject 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Subject 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Subject 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Subject 13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Subject 14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NOTE:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a.
[/TD]
[TD="colspan: 8"]all course graduation requirements, documenting course details and terminal objectives;[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b.[/TD]
[TD="colspan: 12"]all instructor course packages, including all course syllabi, Training Materials, Training aids and instructors notes and guides;[/TD]
[/TR]
[TR]
[TD]c.[/TD]
[TD="colspan: 3"]all Training delivery plans;[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d.[/TD]
[TD="colspan: 6"]all manufacturers’ handbooks used in the delivery of Training;[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e.[/TD]
[TD="colspan: 4"]all student study guides and workbooks;[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]f.[/TD]
[TD="colspan: 6"]all Training progress test and graduation specifications; and[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]g.[/TD]
[TD="colspan: 8"]all Training evaluation requirements and requirements for testing student achievement[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Please note that notes section must not be over written.

I think I can move it one sheet, but I cannot think of a way to identify all the sheets listed in the cell.

Here is a link to excel workbook:
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Droplr • Temp Sheet.xlsm

Thanks all,
Cheers
Kunal
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Hi Kunal,

I have a sorta clunky vba solution if you are willing.

Requires three named ranges and two helper columns on the List sheet.

Want to give it a go?

Regards,
Howard
 
Upvote 0
On your List sheet in the Sheet column you have some cells with multiple sheets, 1, 2, 3.
In those rows move the 2 to the next column and the 3 to the next and the same for the other few that have multiple sheet numbers. So that you have three columns with single digits and many blanks in the second and third.
Assuming the column labeled Sheets is col B select as many cell in that column as needed to cover the entries and name it ColB, select the same number of cell in C and name it ColC and same with col D. Columns C and D have just a few entries as you see.

Paste this code in the List sheet vba module and run it.

Code: 
Option Explicit
Sub DataA()
Dim i As Variant
Dim ColB As Range
Dim ColC As Range
Dim ColD As Range
Dim c As Range
Application.ScreenUpdating = False
  For Each c In Range("ColB")
    If c.Value = 1 Then
      c.Offset(, -1).Copy
      Sheets("Course 1").Range("A20").End(xlUp) _
         .Offset(1, 0).PasteSpecial
    End If
    
    If c.Value = 2 Then
      c.Offset(, -1).Copy
      Sheets("Course 2").Range("A20").End(xlUp) _
         .Offset(1, 0).PasteSpecial
    End If
    
    If c.Value = 3 Then
      c.Offset(, -1).Copy
      Sheets("Course 3").Range("A20").End(xlUp) _
         .Offset(1, 0).PasteSpecial
    End If
    
    If c.Value = 4 Then
      c.Offset(, -1).Copy
      Sheets("Course 4").Range("A20").End(xlUp) _
         .Offset(1, 0).PasteSpecial
    End If
    
    
  Next
  
  For Each c In Range("ColC")
    If c.Value = 1 Then
      c.Offset(, -2).Copy
      Sheets("Course 1").Range("A20").End(xlUp) _
         .Offset(1, 0).PasteSpecial
    End If
    
    If c.Value = 2 Then
      c.Offset(, -2).Copy
      Sheets("Course 2").Range("A20").End(xlUp) _
         .Offset(1, 0).PasteSpecial
    End If
    
    If c.Value = 3 Then
      c.Offset(, -2).Copy
      Sheets("Course 3").Range("A20").End(xlUp) _
         .Offset(1, 0).PasteSpecial
    End If
    
    If c.Value = 4 Then
      c.Offset(, -2).Copy
      Sheets("Course 4").Range("A20").End(xlUp) _
         .Offset(1, 0).PasteSpecial
    End If
    
  Next
  
  For Each c In Range("ColD")
    If c.Value = 1 Then
      c.Offset(, -3).Copy
      Sheets("Course 1").Range("A20").End(xlUp) _
         .Offset(1, 0).PasteSpecial
    End If
    
    If c.Value = 2 Then
      c.Offset(, -3).Copy
      Sheets("Sheet2").Range("A20").End(xlUp) _
         .Offset(1, 0).PasteSpecial
    End If
    
    If c.Value = 3 Then
      c.Offset(, -3).Copy
      Sheets("Course 3").Range("A20").End(xlUp) _
         .Offset(1, 0).PasteSpecial
    End If
    
    If c.Value = 4 Then
      c.Offset(, -3).Copy
      Sheets("Course 4").Range("A20").End(xlUp) _
         .Offset(1, 0).PasteSpecial
    End If
    
  Next
  
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub

Regards,
Howard
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Move the data from one sheet to another
Replies
6
Views
733
jolivanes
jolivanes
M
  • Solved
Macro to copy listed sheets to a new file and save at same directory
Replies
1
Views
787
breynolds0431
B
D
  • Question Question
VBA - Split Source file-Find oldest date in column. Copy all rows with date to new sheet, Repeat on next oldest date. Also, remove some unwanted data.
Replies
0
Views
1K
DK643
D
S
  • Question Question
VBA code to automatically send several different emails (body different) based on cell data
Replies
0
Views
1K
Schanemans
S
M
  • Question Question
Excel formula combine in different sheet
Replies
8
Views
960
Marcelo Branco
M

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top