Update row in Excel and move updated row to the next empty row without creating Duplicates

[peels2141]

Hi

I'm working with a massive excel workbook. In the workbook there is a column with a number of item codes (Column A), On my userfrom there is a textbox that finds the item code and populates a load of textboxes on the userfrom with all infromation form that row. It then allows the user to change any of the textboxes he wants and he presses a command button (Add Details), the new updated version then finds the next empty row and goes there successfully.

The problem with this is that there are now 2 rows with the same item code, I wonder is it possible that when i click the command button Add Details that it would delete the previous row where the item code was and leave the new updated version.

Thanks

[aydeegee]

Hi peels2141

In a word - yes.

My initial thought was why put a new entry - why not just overwrite the original? If you let me see what you have, I can probably suggest something.

Regards
Alastair

[peels2141]

Hi aydeegee,

Thanks for the reply, ya im open to any suggestions

here is my code for poplating the textboxes

Please Login or Register  to view this content.

And then here is the code for the command button (Add Details)

Please Login or Register  to view this content.

Thanks Again

[aydeegee]

Hi peels2141

So now you may have a duplicate entry. If the ProductID matches then will all other columns match? If not, do you want to keep the earliest, the latest or both?

Assuming that the other columns will match and you want to delete the latest, in column I or later, row 1 use the formula

Please Login or Register  to view this content.

Delete any row greater than 1 in this column.

Let me know if I have missed anything.

Regards
Alastair

[peels2141]

Hi aydeegee

Some of the columns will vary but it might just be one or two, because i might have changed the price or something like that in the updated version, but I would want the whole row deleted anyway. I would want to keep the latest updated version and delete the earliest if possible?

Thanks for the Help again
Peels

[aydeegee]

Hi Peels

There may be a neater way, but the formula

Please Login or Register  to view this content.

entered on line 1 will highlight all lines where there is a later match. These lines may be deleted.

Regards
Alastsir

[peels2141]

Hi Alastair

I put that fromula into the first line above the column and nothing happened?

Regards
Peels

[aydeegee]

My apologies - I omitted after "entered on line one" "and copied down to the end of your data"

Regards
Alastair

[peels2141]

H Alastair

I put the formula in a free column at the end and it worked perfectly, it showed me where all the duplicates were so thank you very much.

Thanks Again
Peels

[aydeegee]

Glad it worked ok.

Regards
Alastair