Help! Please!

[Rob Edgeler]

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.

 

[Rob Edgeler]

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.

 

[Frank Kabel]

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" & 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

 

[Tom Ogilvy]

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.