Sort duplicate fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

madgunnercurz - 14 Feb 2007 09:32 GMT

I currently have a spreadsheet setup where when you put data into cell
A2 the current time automatically inputs into cell B2. I now however
would like to know how to set it up so then it finds the duplicate of
colomn A in this instance (A3) but then inserts the duplicate B (B3)
and move this data into colomn C (C2).

>From this layout;

A | B | C
1|
2| 1234 12:30
3| 1234 15:00

Into this layout;
A | B | C
1|
2| 1234 12:30 15:00
3|

So then once the duplicate data is inputted it moves the time to C2
and deletes the duplicate in colomn A. If this cannot be done in real
time is there anyway to manipulate the data(without having to manually
check through all the records) after it has been inputted.

Many Thanks,
MGC

Reply to this Message

Martin Fishlock - 14 Feb 2007 11:41 GMT

You need to use an event macro.

Some clarification is requried?

Can the data be entered anywhere in column A?

If another 1234 is entered (third one) do the other two get moved to the
left?

What happens if the number of dates = 255 (xls.2007=16383)?

Basically you want to use countif(A:A,currentcell)>1 then a duplicate.

then use the index and match to move them around.

Signature

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

> I currently have a spreadsheet setup where when you put data into cell
> A2 the current time automatically inputs into cell B2. I now however
[quoted text clipped - 20 lines]
> Many Thanks,
> MGC

Reply to this Message

madgunnercurz - 15 Feb 2007 07:49 GMT

I am setting this up so then users of the training centre can come and
go as they please while the staff are busy working with learners. So
in colomn A 1234 could sign in at the start of the day and then sign
out after 3 hours which more people would of signed in/out in that
time. I have tried access for this but i am useless at VBA code and i
am goin to import the data into access when i figure this out.

Reply to this Message

Martin Fishlock - 15 Feb 2007 08:11 GMT

Hi:

Why don't you have two buttons on your worksheet with sign in and sign out

and open a user form to sign in and sign out.

Your ie they enter their id and press ok and the vba does the rest

when they leave they click the other button and they enter the id and they
are removed

that way you control all the stuff.

you can do that in excel or access directly into a table.

Signature

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

> I am setting this up so then users of the training centre can come and
> go as they please while the staff are busy working with learners. So
> in colomn A 1234 could sign in at the start of the day and then sign
> out after 3 hours which more people would of signed in/out in that
> time. I have tried access for this but i am useless at VBA code and i
> am goin to import the data into access when i figure this out.

Reply to this Message

madgunnercurz - 15 Feb 2007 12:33 GMT

This is how it was originally setup in an access database, however
with no one been able to tend to the machine a group of young
teenagers thought it would be fun to delete all the records which cost
us around £5,000. So we are wanting the user to only touch a barcode
scanner so then there is no repeat.

Reply to this Message

Joel - 14 Feb 2007 12:22 GMT

RowCount = 1
Do Until Cells(RowCount, 1).Value = ""
if Cells(RowCount, 2).Value = Cells(RowCount + 1, 2).Value
Cells(RowCount, 3).Value = Cells(RowCount + 1, 2).Value
Range(Cells(RowCount + 1, 1), Cells(RowCount + 1, 3)).Delete

End If

RowCount = RowCount + 1

Loop

> I currently have a spreadsheet setup where when you put data into cell
> A2 the current time automatically inputs into cell B2. I now however
[quoted text clipped - 20 lines]
> Many Thanks,
> MGC

Reply to this Message