+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    10-08-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    26

    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....
    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.
    Last edited by HankMcSpank; 12-01-2013 at 02:25 PM.

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

    Ηi

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

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Edit:

    Or add this in a button.

    Please Login or Register  to view this content.
    Last edited by Fotis1991; 12-01-2013 at 02:49 PM. Reason: Edit
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    10-08-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    26

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

    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!

  4. #4
    Registered User
    Join Date
    10-08-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    26

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

    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!

  5. #5
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

    Depands on how many different combinations could have.

    Your simple example can works using something like this..

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-08-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    26

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

    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!
    Last edited by HankMcSpank; 12-01-2013 at 05:18 PM.

  7. #7
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

    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: copy to clipboard
    Please Login or Register  to view this content.


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

  8. #8
    Registered User
    Join Date
    10-08-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    26

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

    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!

  9. #9
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

    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

  10. #10
    Registered User
    Join Date
    10-08-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    26

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

    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) :-)
    Last edited by HankMcSpank; 12-01-2013 at 06:56 PM.

  11. #11
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

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

  12. #12
    Registered User
    Join Date
    10-08-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    26

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

    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?!
    Last edited by HankMcSpank; 03-07-2014 at 08:10 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Searching for duplicated numbers
    By ridnickhick in forum Excel General
    Replies: 5
    Last Post: 07-26-2012, 06:39 PM
  2. Using Vlookup for duplicated numbers
    By fodejimi in forum Excel General
    Replies: 5
    Last Post: 05-12-2011, 09:10 AM
  3. Renumbering a list of numbers
    By Dr.Alvixagon in forum Excel General
    Replies: 1
    Last Post: 03-16-2006, 05:10 PM
  4. [SOLVED] sales record and receipts
    By BHAVIN in forum Excel General
    Replies: 1
    Last Post: 07-08-2005, 04:05 AM
  5. [SOLVED] Excel Adding duplicated numbers together
    By JJ Joobler in forum Excel General
    Replies: 1
    Last Post: 01-06-2005, 10:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1