Renumbering sales receipts, but catering for duplicated original numbers - please help!

[HankMcSpank]

I have a spreadsheet containing all my 'old' sales receipt numbers in column A, I now want to renumber them in column 'B', starting at 1, but I want any duplicate values showing in column 'A', to end up with the same number.

For example....

Please Login or Register  to view this content.

...could someone please start me off on how I can get macro to do this?!

Essentially the logic here is (i.e. if sampling all the way down column 'A')....

"if the present 'cell' contents is the same as the last sampled cell above then the 'newly generated' sales number is the same as the last one"

Many thanks in anticipation.

[Fotis1991]

Ηi

Assuming your data start in A2, then In B2 type 1. In B3 and copy down

Please Login or Register  to view this content.

Edit:

Or add this in a button.

Please Login or Register  to view this content.

[HankMcSpank]

I went for your second option & it works marvelously - thank you so much :-)

(that's a whole heap of my manual time - prone to errors - saved!

Many thanks!

[HankMcSpank]

At the risk of pushing my luck, how would the following be approached...

If the contents of cell A1 is "fish" & the contents of cell B1 is "chips" then make the contents of cell C1 "dinner" (it was the first example that came into my head to show what I'm trying to achieve!).I have to put a value in the final column depending on what the contents of two columns to the left are.

again heading down a spreadsheet row by row.

my problem is that there a re a lot of permutations so would some form of look up table be needed?

no probs if it's asking a bit much, but the task is onerous for me, so I thought I'd ask!

[Fotis1991]

Depands on how many different combinations could have.

Your simple example can works using something like this..

Please Login or Register  to view this content.

[HankMcSpank]

The problem is I need the first 'condition' to be any country from a region of countries (EU, non EU) & the second condition to be a currency, therefore heading down a spreadsheet row by row....

Please Login or Register  to view this content.

There are 5 currencies in play (AUD, CAD, EUR, GBP & USD) & the EU countries are ...

Austria
Belgium
Bulgaria
Croatia
Cyprus
Czech Republic
Denmark
Estonia
Finland
France
Germany
Greece
Hungary
Ireland
Italy
Latvia
Lithuania
Luxembourg
Malta
Netherlands
Poland
Portugal
Romania
Slovakia
Slovenia
Spain
Sweden
United Kingdom

so that's a lot of testing to establish what result should be placed in the final column!

What would be the most efficient approach? (perhaps it needs a new thread?)

At the minute I have all my data in a spreadsheet & I'm filtering based on a macro array...then copying to a new spreasheet & saving it....but I'd much rather just tag a new column on the end of my spreadsheet with all the data & run a macro to populate the final column with the outcomes of the test!

[Fotis1991]

For sure this is completely different from what you described in your first post!

1 approach could be this.

Name your range with Europeans coundries. Let's say that we give the name "List"--without the quotes-

Please Login or Register  to view this content.

Also keep in mind that a small sample workbook is always useful.

[HankMcSpank]

I'm sorry, I didn't understand your reply, but this question is probably a big ask anyway, so I'll have a bit of a google!

[Fotis1991]

The question is normal but as i already mentioned, i need a sample workbook to show you how to do this!

Be sure that best way to describe your problem is to upload a sample workbook.

Be sure that all sensitive data removed, showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that.

To attach a small sample workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[HankMcSpank]

I'll try to upload a small sample a bit later, but taking the path of least resistance (or a more accurate description....a kludge!), I've just realised that I can first test whether a county is EU or not with this somewhat long & cumbersome formula (the country information in my spreadsheet is actually in cell AB2)...

Please Login or Register  to view this content.

(I just enter it into a cell & drag it down my spreadsheet.)

the result of that is either EU or nonEU

I can then construct the final I require value from that (i.e. by adding that result to cell values taken from a couple of other columns) :-)

[Fotis1991]

The countif function that i suggested, does Exactly what's your long IF(OR... formula does.

[HankMcSpank]

I know this is an excel forum, but can anyone recommend how this same autonumbering as outlined/solved above could be done in Access - I can't use Access's built in autonumber functionality, because Access will only allocate (autonumber) one unique number per row, whereas if you check further up this thread, you'll see that for a situation when an order ID arrives that's identical to the previous, then not to increment the auto-number.

Any ideas?!