Turn Acct List to Work Lists?

[ChristianCoffeecup]

I work in a call center. I have a hot boss that I'd like to impress.

Anyways, She spends an hour a day turning a huge list of accounts, into smaller lists for 20 people to work. I'd appreciate it if someone could help me impress her and save her TONS of time.

Here's the problem:

I have a list of 400 accounts in Column A

Now, on the side, I have a Table with the names of each person across the top.

Example: Person 1, Person 2, Person 3, etc.

How can I take this column of accounts, and automatically put the first 20 results under Person 1's column, and the next 20 results under Person 2's column, and so on?

 

[SydneyGeek]

Not perfect but it's a starting point...

Setup is --
Column A, accounts (no header) starting in A3 and going down
C1:V1 are numbers 1 to 20
C2:V2 are names of call center personnel

NOTHING ELSE in Column A or Row 2

B1 formula is =CEILING((COUNTA(A:A)/COUNTA(2:2)),1)
C3 formula is =OFFSET($A$1,$B$1*(C$1-1)+ROW()-1,0)

Fill C3 formula down and across.

Denis

 

[ChristianCoffeecup]

Woo! Hoo!

I think I may have found that "Miracle Worker" that Bones from Star Trek keeps saying that he isn't.

Ty Ty Ty!

Anyone else have a suggestion?

 

[Fazza]

Hi Christian. Please test this suits your set up. Regards, Fazza

Sub for_your_hot_boss()
 
  'have set this to apply to the activesheet
 
  'please test in a copy of the file - just in case
 
  'have assumed source data starts in cell A2
  Const strAddressOfFirstDataCell As String = "A2"
 
  'have assumed first destination column 2, (column B)
  Const lngFirstDestinationColumn As Long = 2
 
  'have assumed this many rows per destination column
  Const lngRowsPerDestination As Long = 20
 
  Dim i As Long
  Dim rngData As Range
 
  With ActiveSheet
 
    'erase any pre-existing data in the destination cells
    .Cells(2, lngFirstDestinationColumn).Resize(lngRowsPerDestination, .Columns.Count - lngFirstDestinationColumn + 1).ClearContents
 
    'if there is no source data, exit
    If Len(.Range(strAddressOfFirstDataCell).Value) = 0 Then Exit Sub
 
    Set rngData = Range(.Range(strAddressOfFirstDataCell), .Cells(.Rows.Count, .Range(strAddressOfFirstDataCell).Column).End(xlUp))
 
    For i = 1 To rngData.Rows.Count Step lngRowsPerDestination
      .Cells(2, lngFirstDestinationColumn).Offset(, (i - 1) / lngRowsPerDestination).Resize(lngRowsPerDestination).Value = rngData.Cells(i).Resize(lngRowsPerDestination).Value
    Next i
 
    'if you want to clear the source data, uncomment this next line
    'rngData.ClearContents
 
  End With
 
End Sub

 

[ChristianCoffeecup]

Okay. Now it's a little more complicated...

There's actually 3 columns of information like this:

Column A: Account Numbers
Column B: Order Full Fillment (usually Kept or Pending)
Column C: Days Since Order

I need to do the same thing as before, but each person needs their own work list of 20 accounts, and they each need 3 columns of information (acct #, Order Status, Days Since Order)

Each Person Will have 3 columns, Example:

Person 1 ........................................... Person 2 .....................................
(acct #, Order Status, Days Since Order), (acct #, Order Status, Days Since Order),


How do I take the information in Columns A, B, and C .. and auto generate lists of 20 accounts (including order status and days since order) for each person's worklist?

Oh, and I'm prohibited from using VBA or Scripts at work. This would have to be done with formulas only.

 

[SydneyGeek]

Insert 2 columns between each of the 20 Person columns. Keep the formulas as is.
Then use VLOOKUP to pull the second column for each person, and a date calculation in the third column.

Denis

 

[ChristianCoffeecup]

This works great with a regular ascending list of numbers 1-400. But, when I put in random 7 digit account numbers, the V Look up and H Look up go wild.

I'm thinking that simply Copy & Pasting "Linked Cells" would work better.

I could have a page with each model:
Sheet 2: results for 5 accts per person,
Sheet 3: results for 6 accounts per person,
... and so on.

Would take some time to put together. But, could save her some time at work.