Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weird Problem..hoping to find a solution
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weird Problem..hoping to find a solution
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 / On Mar 20, 4:14 pm, "Gary" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weird Problem..hoping to find a solution
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 "Tyla" wrote in message ups.com... 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 / On Mar 20, 4:14 pm, "Gary" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weird Problem..hoping to find a solution
and yes you are right....kind of summary stats.
"Tyla" wrote in message ups.com... 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 / On Mar 20, 4:14 pm, "Gary" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weird Problem..hoping to find a solution
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 ----------------------------- On Mar 20, 6:15 pm, "Gary" wrote: and yes you are right....kind of summary stats. "Tyla" wrote in message ups.com... 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/ On Mar 20, 4:14 pm, "Gary" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum if problem, trying to find best solution | Excel Discussion (Misc queries) | |||
simple i am hoping with macros | Excel Discussion (Misc queries) | |||
please find a solution | Excel Discussion (Misc queries) | |||
Weird problem | Excel Worksheet Functions | |||
Weird Problem | Excel Worksheet Functions |