Weird Problem..hoping to find a solution

[Gary]

Hi All,

Here's what i am trying to accomplish.

On Sheet2 I have name of the person in column A, name of the queue he has
worked on in column B, time in column C and count in column D. It is
possible to have one name upto 11 times if that guy worked on all the
queues. on sheet1 I have just the names in column A. Now what I want excel
to do is....

Lookup the name in sheet2, return the first queue he worked on in column B,
If the name is repeated in Sheet2 then insert a row below and return the
second queue and so on. After this....look for a match of both, the name and
the queue and return the time in Colum C and count in Column D.

it will be easier if we can somehow eliminate the 'Inserr Row' Part.

I tried my best to explain the problem.

Hoping to find a solution.

Thanks in advance
Gary

 

[Tyla]

Gary,

I'm confused: Where does all this insertion take place? Same
spreadsheet, different spreadsheet? Can you describe the final data
layout you're looking for? Part of me is sensing you're wanting
summary statistics per name, but it reads like you just want do insert
a row for every row in the raw data. So what am I missing here?

/ Tyla /

 

[Gary]

Hi Tyla,

The final data will be something like this.

Column A-Column B- Column C - Column D
Name1 Queue1 2:30 20
Name1 Queue2 1:00 10
Name 2 Queue1 3:00 5
Name 3 Queue1 1:00 15
Name 3 Queue2 3:00 25
Name 3 Queue3 4:00 20

Thanks

 

[Gary]

and yes you are right....kind of summary stats.

Gary,

I'm confused: Where does all this insertion take place? Same
spreadsheet, different spreadsheet? Can you describe the final data
layout you're looking for? Part of me is sensing you're wanting
summary statistics per name, but it reads like you just want do insert
a row for every row in the raw data. So what am I missing here?

/ Tyla /

 

[Tyla]

Gary,

I wouldn't be surprised if someone comes up with a solution with pure
Excel cell formulas, but here's a start of a VBA solution. It's not
optimized code at all, and makes all sorts of assumptions about the
completeness of your source data, worksheet names, etc. but it may be
enough to get you started. One big assumption is that the raw data is
sorted by name and queue. The other big assumption is that the queue
time and the queue name change together. That is, if you know one you
know the other, within a given person's name. (If this isn't true,
it's just adding another layer of 'If/Then' statements.) Anyway,
here's the code. Hope it helps

/ Tyla /
-----------------------------
Option Explicit

Private miDestRow As Integer


Sub summarizeQueues()

Dim dSourceRow As Double

Dim sCurrentName As String
Dim sPreviousName As String
Dim sCurrentQueue As String
Dim sPreviousQueue As String


' Init
miDestRow = -1
dSourceRow = 0
sPreviousName = ""
sPreviousQueue = ""
Worksheets("Sheet1").Select
sCurrentName = Range("A1").Value
sCurrentQueue = Range("A1").Offset(0, 2).Value


' Walk through the raw data until you run into a blank for name
While sCurrentName <> ""
' If there's a new name, add a new row (and remember)
If (sCurrentName <> sPreviousName) Then
Call addNewDesinationtRow(dSourceRow)
sPreviousName = sCurrentName
sPreviousQueue = sCurrentQueue
Else
' If a new queue for same name, add a new destination row
If (sCurrentQueue <> sPreviousQueue) Then
Call addNewDesinationtRow(dSourceRow)
sPreviousQueue = sCurrentQueue
Else
' Just update the counter in the current destination
row
Worksheets("Sheet2").Range("A1").Offset(miDestRow,
3).Value = Worksheets("Sheet2").Range("A1").Offset(miDestRow, 3).Value
+ Worksheets("Sheet1").Range("A1").Offset(dSourceRow, 3).Value
End If


End If
dSourceRow = dSourceRow + 1
sCurrentName = Range("A1").Offset(dSourceRow, 0).Value
sCurrentQueue = Range("A1").Offset(dSourceRow, 1).Value
Wend


End Sub

Sub addNewDesinationtRow(dSourceRow As Double)

' Update the pointer to the destination row
miDestRow = miDestRow + 1

' Copy the data over (might be easier to copy the whole row,
depending)
Worksheets("Sheet2").Range("A1").Offset(miDestRow, 0).Value =
Worksheets("Sheet1").Range("A1").Offset(dSourceRow, 0).Value
Worksheets("Sheet2").Range("A1").Offset(miDestRow, 1).Value =
Worksheets("Sheet1").Range("A1").Offset(dSourceRow, 1).Value
Worksheets("Sheet2").Range("A1").Offset(miDestRow, 2).Value =
Worksheets("Sheet1").Range("A1").Offset(dSourceRow, 2).Value
Worksheets("Sheet2").Range("A1").Offset(miDestRow, 3).Value =
Worksheets("Sheet1").Range("A1").Offset(dSourceRow, 3).Value



End Sub