+ Reply to Thread
Results 1 to 23 of 23

single sheet, list of transactions- in and out. How to identify incomplete transactions?

  1. #1
    Registered User
    Join Date
    09-17-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    15

    single sheet, list of transactions- in and out. How to identify incomplete transactions?

    moved from the big boys forum to here. apologies for cross posting...

    I have a single worksheet of over 8000 lines. They are all transaction that are identified by an email address. Every 'in' should have an associated 'out' on the same email address. How do I run a query (and is that the best way..?) to identify the 'in' transactions that dont have a corresponding 'out' transaction?

    Yes, you guessed, I am almost complete novice to using excel other than for adding up and taking away long lists of numbers.

    Any help. suggestions, tutorials, previous posts much appreciated.

    Thanks for your time.

    Administrative Note:
    • Thank you for implying we are a lesser forum, but…
    • Please see Forum Rule #8 about cross posting and adjust accordingly...
    • Added for you this time, but please do it in the future…Thanks.
    • Cross post
    Last edited by jeffreybrown; 09-17-2013 at 06:37 PM.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    You don't need a query, you just need to extract or highlight the rows that are "on the outs"

    Mr. Excel aka "big boys"
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    09-17-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    Hi

    Thanks for your thoughts. I have 8000+ records. I need to establish which of the email identifiers attached to each transaction are not a complete transaction as in one'in'+one'out' for each email address=complete transaction. The remaining orphaned transactions plus the email address attached to it is the set of data i am looking to end up with.

    Not sure I understand how highlighting the rows will give me that dataset. Am i being very dim?

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    Highlighting would make them easy to spot - probably better to extract them to another sheet - or you could just copy the orphans to another sheet and highlight the orphans on the original sheet - can you post a sample workbook?

    (Go to advanced and click on the paper clip etc)

  5. #5
    Registered User
    Join Date
    09-17-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    oh and the move from big boys to here referred to me moving from the general questions area, to here - new users and basic questions. I was not referring to the t fact I posted this same question on an entirely different forum in a desparate attempt to find answers to the question that is currently driving me insane with impotent fury and causing me to bang my head repeatedly on a poor defenseless laptop. My apologies for any inferred slight upon this magnificent forum and the august assembly who graciously share their knowledge with people like me.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    A guy was seeking divine help in winning the lottery - the divinity asked him to meet him/her halfway and buy a ticket

    can you post a sample workbook? (Go to advanced and click on the paper clip etc)

  7. #7
    Registered User
    Join Date
    09-17-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    goingthrough line by line for about 40 minutes and still being in the email addresses that started with 'a' made think there must be a better way...

  8. #8
    Registered User
    Join Date
    09-17-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    transactionquery test spread.xlsxattached is some sample data

  9. #9
    Registered User
    Join Date
    09-17-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    i thin what i need to do is something lie this:

    Does "email addressX" in column 3 have "receive" yes/no
    Does "email addressX" in column 3 have "refund" yes/no
    If no to one of the above, then note this line in the following way and add to list

    loop through 8000+ lines

    I hope that the output would be a list of anomalous email addresses that have either an 'in' and no 'out' or an 'out' but no 'in'

    as you can see, I am not a programmer.
    Last edited by IDontGetIt; 09-17-2013 at 08:51 PM. Reason: missed out important word

  10. #10
    Registered User
    Join Date
    09-17-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    although it has just occurred to m that there are multiple instances of the same email address. what I need to do is check all instances of the same email address and see if there is a mismatch between ins and outs. i.e. the same email may appear several times and have ins and outs, but there may be a disparity in only one paired transaction.

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    It looks like you need to consider the entries by order number - I'll spend some time with it tomorrow - but I'm in California - so if anyone else wants to help, they should feel free

  12. #12
    Registered User
    Join Date
    09-17-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    Morning xladept

    I am other side of the planet,burning midnight oil last night. Thanks for taking an interest and look forward to any help you may be able to give me. Sadly my subconcious came up with zero overnight. Am trying a caffeine based approach this morning.

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    Hi IDG,

    I've grouped them by order number and made comments in the "G" Column - take a look and see whether we can work with it transactionqueries.xlsm

  14. #14
    Registered User
    Join Date
    09-17-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    looks exactly as I would like it to appear. Is it possible to automate that output? Otherwise it will be a long week...

    Thanks for your time in looking at this.

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    Hi IDG,

    The code is already in the module - I thought that you would need more than that - do you need instructions on how to use it?

  16. #16
    Registered User
    Join Date
    09-17-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    man i feel pretty stupid.i have to confess I dont even know what a module is, i couldn't see anything that looked like a formula in any of the cells.

    The sad but true answer is yes. Please could you explain what you ahve done, because to me it looks like magic.

    Thanks

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    Hi IDG,

    The first thing to do is save the workbook I sent you as macro-enabled and leave it up.

    Then bring up the workbook you want to process and leave it active.

    The easiest way to run the program then is just hit Alt-F8 and double click on IDG (the macro name).

  18. #18
    Registered User
    Join Date
    09-17-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    09-17-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    OrdNo = Cells(i, 6) is the yellowed out line

  20. #20
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    Hi IDG,

    I don't know how the actual data differs from the sample - but try this:

    Please Login or Register  to view this content.
    Just copy and paste over the other code.

    or - if you have more than one header row, make the i start at the first row with data in

    Please Login or Register  to view this content.
    Last edited by xladept; 09-18-2013 at 07:08 PM.

  21. #21
    Registered User
    Join Date
    09-17-2013
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    Please Login or Register  to view this content.
    is highlighted.

    In the tranaction column these are the four possible values:
    Please Login or Register  to view this content.
    Additionally there are now two header rows. A new one in red and the original.

  22. #22
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    Change:
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    And, the transaction column is Column two on the sample - should you send me another, more true, sample?

  23. #23
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: single sheet, list of transactions- in and out. How to identify incomplete transaction

    Hi IDG,

    What's the story?

+ 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. Replies: 2
    Last Post: 09-17-2013, 07:12 PM
  2. Sort by Stock Name then list by Oppsite Transactions
    By SMJE in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-19-2011, 06:57 PM
  3. pay pal transactions
    By networkmom in forum Excel General
    Replies: 1
    Last Post: 02-18-2008, 01:42 AM
  4. Match a single deposit to seperate transactions
    By clap4thehandica in forum Excel General
    Replies: 3
    Last Post: 01-16-2008, 02:18 PM
  5. [SOLVED] Creating a budget from a list of transactions
    By Mctabish in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2005, 10:05 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