Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Please!
Hello. I'm trying to create a function in Excel that will populate empty
cells with a value. I have a file, which is imported from another system that contains the details of operators that have signed on to a specific checkout in the retail store in which I work, this file is in Excel format. The imported list contains about 13000 rows, each one containing the name of the operator that signed on. It looks like this: Checkout Number Operator Number Operator Name 1 123 A Cashier 124 B Cashier 125 C Cashier 126 D Cashier 2 127 E Cashier 128 F Cashier 129 G Cashier 3 130 H Cashier etc etc. As you can see, the imported data does not contain the checkout number in every row which I would like it to do. Each week, when I run a new report for the previous week, the number of rows for each checkout is different, dependant on who's signed on that week. Could anybody help with providing a formula that I can run in a macro that will populate the checkout number for me that doesn't require me to specify the range myself of where checkout 1 ends and checkout 2 starts? With my limited knowledge of macros, I'm finding this really hard. I feel sure I need to use a loop, but there are several sorts, and I don't know which sort to use or how to go about it. To further complicate the issue, the system that I import the data from shows the same cashier against the same checkout a number of times, as it captures *each* sign-on rather than just consolidating all the sign-ons to one cashier. For instance, B Cashier could sign on to checkout 2 18 times during the week, and this is what is making the number of rows so huge. I would also like to be able to run a macro to remove the duplicates in the operator name field on each checkout, so that in effect even though B Cashier has signed on to checkout 1 a lot of times, it still shows that they have signed onto checkout 2 a number of times also. I realise that this sort of thing maybe better controlled in Access, but we do not have Access installed at work and we will not be getting it either. I work for a national company and all their PC's are built to the same specification and have the same functions installed on them. There is also no hope of changing the system where the imported data originates as again, this is a vanilla system and cannot be modified. I really hope that someone can help...I know its possible, but just can't even begin to think how to do it effectively! Kind Regards Rob. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Please!
Sorry, but the word "Cashier" should appear as part of the Operator Name, ie
A Cashier, B Cashier etc and not shown on a different line. I've obviously set my tabs wrong! Rob. "Rob Edgeler" wrote in message ... Hello. I'm trying to create a function in Excel that will populate empty cells with a value. I have a file, which is imported from another system that contains the details of operators that have signed on to a specific checkout in the retail store in which I work, this file is in Excel format. The imported list contains about 13000 rows, each one containing the name of the operator that signed on. It looks like this: Checkout Number Operator Number Operator Name 1 123 A Cashier 124 B Cashier 125 C Cashier 126 D Cashier 2 127 E Cashier 128 F Cashier 129 G Cashier 3 130 H Cashier etc etc. As you can see, the imported data does not contain the checkout number in every row which I would like it to do. Each week, when I run a new report for the previous week, the number of rows for each checkout is different, dependant on who's signed on that week. Could anybody help with providing a formula that I can run in a macro that will populate the checkout number for me that doesn't require me to specify the range myself of where checkout 1 ends and checkout 2 starts? With my limited knowledge of macros, I'm finding this really hard. I feel sure I need to use a loop, but there are several sorts, and I don't know which sort to use or how to go about it. To further complicate the issue, the system that I import the data from shows the same cashier against the same checkout a number of times, as it captures *each* sign-on rather than just consolidating all the sign-ons to one cashier. For instance, B Cashier could sign on to checkout 2 18 times during the week, and this is what is making the number of rows so huge. I would also like to be able to run a macro to remove the duplicates in the operator name field on each checkout, so that in effect even though B Cashier has signed on to checkout 1 a lot of times, it still shows that they have signed onto checkout 2 a number of times also. I realise that this sort of thing maybe better controlled in Access, but we do not have Access installed at work and we will not be getting it either. I work for a national company and all their PC's are built to the same specification and have the same functions installed on them. There is also no hope of changing the system where the imported data originates as again, this is a vanilla system and cannot be modified. I really hope that someone can help...I know its possible, but just can't even begin to think how to do it effectively! Kind Regards Rob. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Please!
Hi
for the first part of your question use a macro like the following: Sub add_checkout() Dim RowNdx As Long Dim LastRow As Long application.screenupdating=false LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).row For RowNdx = 3 to LastRow If Cells(RowNdx, "A").value="" then Cells(RowNdx, "A").value = Cells(RowNdx, "A").offset(-1,0).value End If Next RowNdx application.screenupdating=True End Sub for the second part use a macro like the following to remove duplicates (uses column D as helper column): Public Sub DeleteDuplicateRows() Dim RowNdx As Long Dim LastRow As Long Dim V Application.ScreenUpdating = False With ActiveSheet LastRow = .Cells(Rows.Count, "B").End(xlUp).Row .Range("D2:D" & LastRow).FormulaR1C1 = "R[0]C1 & " ^ " & R[0]C2" For RowIndex = LastRow To 2 Step -1 V = .Cells(RowIndex, "D").Value If Application.WorksheetFunction.CountIf(.Columns(4), V) 1 Then .Rows(r).EntireRow.Delete End If Next RowIndex .Columns(4).ClearContents End With Application.ScreenUpdating = True End Sub -- Regards Frank Kabel Frankfurt, Germany Rob Edgeler wrote: Hello. I'm trying to create a function in Excel that will populate empty cells with a value. I have a file, which is imported from another system that contains the details of operators that have signed on to a specific checkout in the retail store in which I work, this file is in Excel format. The imported list contains about 13000 rows, each one containing the name of the operator that signed on. It looks like this: Checkout Number Operator Number Operator Name 1 123 A Cashier 124 B Cashier 125 C Cashier 126 D Cashier 2 127 E Cashier 128 F Cashier 129 G Cashier 3 130 H Cashier etc etc. As you can see, the imported data does not contain the checkout number in every row which I would like it to do. Each week, when I run a new report for the previous week, the number of rows for each checkout is different, dependant on who's signed on that week. Could anybody help with providing a formula that I can run in a macro that will populate the checkout number for me that doesn't require me to specify the range myself of where checkout 1 ends and checkout 2 starts? With my limited knowledge of macros, I'm finding this really hard. I feel sure I need to use a loop, but there are several sorts, and I don't know which sort to use or how to go about it. To further complicate the issue, the system that I import the data from shows the same cashier against the same checkout a number of times, as it captures *each* sign-on rather than just consolidating all the sign-ons to one cashier. For instance, B Cashier could sign on to checkout 2 18 times during the week, and this is what is making the number of rows so huge. I would also like to be able to run a macro to remove the duplicates in the operator name field on each checkout, so that in effect even though B Cashier has signed on to checkout 1 a lot of times, it still shows that they have signed onto checkout 2 a number of times also. I realise that this sort of thing maybe better controlled in Access, but we do not have Access installed at work and we will not be getting it either. I work for a national company and all their PC's are built to the same specification and have the same functions installed on them. There is also no hope of changing the system where the imported data originates as again, this is a vanilla system and cannot be modified. I really hope that someone can help...I know its possible, but just can't even begin to think how to do it effectively! Kind Regards Rob. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Please!
Select column A
do edit=Goto=special and select blank cells. Now all the blank cells should be selected with cell A3 as the active cell In the formula bar type =A2 then do ctrl+enter rather than just enter this should produce the checkout number, but it is produced by a formula you can then reselect column A and do Edit=Copy immediately to Edit=Pastespecial and select values to replace the formulas with the number displayed. for problem 2, go to the next visible column (assume it is column E) In E2 put in the formula =A2&B2 then drag fill down the column in F2 put in =if(countif($E$2:E2,E2)1,"Delete","Keep") then drag fill this down the column Select A1 and do Data=Filter=Autofilter In column F, in the dropdown, select Delete Now select all your data (not including row 1) and do edit=Delete and delete the entire row. Only the visible rows will be deleted. No do Data=filter=Autofilter to remove the filter. Now delete columns E and F. -- Regards, Tom Ogilvy "Rob Edgeler" wrote in message ... Hello. I'm trying to create a function in Excel that will populate empty cells with a value. I have a file, which is imported from another system that contains the details of operators that have signed on to a specific checkout in the retail store in which I work, this file is in Excel format. The imported list contains about 13000 rows, each one containing the name of the operator that signed on. It looks like this: Checkout Number Operator Number Operator Name 1 123 A Cashier 124 B Cashier 125 C Cashier 126 D Cashier 2 127 E Cashier 128 F Cashier 129 G Cashier 3 130 H Cashier etc etc. As you can see, the imported data does not contain the checkout number in every row which I would like it to do. Each week, when I run a new report for the previous week, the number of rows for each checkout is different, dependant on who's signed on that week. Could anybody help with providing a formula that I can run in a macro that will populate the checkout number for me that doesn't require me to specify the range myself of where checkout 1 ends and checkout 2 starts? With my limited knowledge of macros, I'm finding this really hard. I feel sure I need to use a loop, but there are several sorts, and I don't know which sort to use or how to go about it. To further complicate the issue, the system that I import the data from shows the same cashier against the same checkout a number of times, as it captures *each* sign-on rather than just consolidating all the sign-ons to one cashier. For instance, B Cashier could sign on to checkout 2 18 times during the week, and this is what is making the number of rows so huge. I would also like to be able to run a macro to remove the duplicates in the operator name field on each checkout, so that in effect even though B Cashier has signed on to checkout 1 a lot of times, it still shows that they have signed onto checkout 2 a number of times also. I realise that this sort of thing maybe better controlled in Access, but we do not have Access installed at work and we will not be getting it either. I work for a national company and all their PC's are built to the same specification and have the same functions installed on them. There is also no hope of changing the system where the imported data originates as again, this is a vanilla system and cannot be modified. I really hope that someone can help...I know its possible, but just can't even begin to think how to do it effectively! Kind Regards Rob. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|