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

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....

Column A     Column B (new sales receipt numbers)

1001           1
1002           2
1003           3
1003           3 
1004           4
1005           5
1006           6

...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.

Ηi

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

Formula:

=IF(A3="","",IF(A3=A2,B2,B2+1))

Edit:

Or add this in a button.

Sub macro1()
   Range("b2") = "1"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(RC[-1]=R[-1]C[-1],R[-1]C,R[-1]C+1))"
    Range("B3").Select
    Selection.AutoFill Destination:=Range("B3:B1000"), Type:=xlFillDefault
    Range("B3:B1000").Select
    Range("B2").Select
End Sub

Depands on how many different combinations could have.

Your simple example can works using something like this..

Formula:

=if(and(a2="fish",b2="chips"),"dinner","")

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....

if       cell A1 = an  EU Country      and cell B1 = EUR then C1 = EU based (EUR)
else if  cell A1 = an  EU Country      and cell B1 = GBP then C1 = EU based (GBP)
else if  cell A1 = an  EU Country      and cell B1 = USD then C1 = EU based (USD)
else if  cell A1 = non EU Country      and cell B1 = EUR then C1 = nonEU based (EUR)
else if  cell A1 = non EU Country      and cell B1 = GBP then C1 = nonEU based (GBP) 

then move down a row, rinse repeat....

if       cell A2 = an  EU Country      and cell B2 = EUR then C2 = EU based (EUR)
else if  cell A2 = an  EU Country      and cell B2 = GBP then C2 = EU based (GBP)
else if  cell A2 = an  EU Country      and cell B2 = USD then C2 = EU based (USD)
else if  cell A2 = non EU Country      and cell B2 = EUR then C2 = nonEU based (EUR)
else if  cell A2 = non EU Country      and cell B2 = GBP then C2 = nonEU based (GBP)

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!

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-

Formula:

=IF(AND(COUNTIF(List,a1),b1="EUR"),"EU",IF(AND(......

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

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

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)...

=IF(OR(AB2="Austria",AB2="Belgium",AB2="Bulgaria",AB2="Croatia",AB2="Cyprus",AB2="Czech Republic",AB2="Denmark",AB2="Estonia",AB2="Finland",AB2="France",AB2="Germany",AB2="Greece",AB2="Hungary",AB2="Ireland",AB2="Italy",AB2="Latvia",AB2="Lithuania",AB2="Luxembourg",AB2="Malta",AB2="Netherlands",AB2="Poland",AB2="Portugal",AB2="Romania",AB2="Slovakia",AB2="Slovenia",AB2="Spain",AB2="Sweden",AB2="United Kingdom"),"EU","nonEU")

(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) :-)

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

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?!