+ Reply to Thread
Results 1 to 10 of 10

reconcile gift card transactions across multiple locations

  1. #1
    Registered User
    Join Date
    01-02-2016
    Location
    NJ, USA
    MS-Off Ver
    15
    Posts
    5

    reconcile gift card transactions across multiple locations

    I've attached a sample of a spreadsheet I've put together to determine which location owes which money pulled from a much more extensive spreadsheet with a lot of extra data...

    So in it's simplest form, it shows where a card was redeemed and for how much as well as which store issued the card originally.

    I am looking to build a sheet that will break down if a card was purchased at store "md" but was redeemed at store "cr" then store "md" owes store "cr" for the redemption.

    I've broken down the gross amounts to be distributed but am having trouble determining how to pull the data for the detail taking into account that many cards were purchased and redeemed at the same location requiring no action on my part.
    Let me know if anyone has any suggestions.

    Thank you.
    -Michael
    Attached Files Attached Files
    Last edited by Mpatalano; 01-03-2016 at 01:46 AM. Reason: solved

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,864

    Re: reconcile gift card transactions across multiple locations

    Hi Welsome to Forum,

    I'm unable to understand what is you actual need.

    Try F9=SUMIFS(redemption,$B$2:$B$148,$E9,$C$2:$C$148,F$8)

    If It is not what you looking for them pls put some desired results in attached file.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Registered User
    Join Date
    01-02-2016
    Location
    NJ, USA
    MS-Off Ver
    15
    Posts
    5

    Re: reconcile gift card transactions across multiple locations

    Thank you for the quick reply...
    I've attached another file that shows the results I'd like.
    In the example, the location called 'ag' had redeemed 5 cards with a total value of 736.81 from 4 other locations.
    the location 'br' had 11 cards redeemed for a total of 213.54 but 5 of those were issued from store 'br' so they don't need to be added to my grid.
    I hope I explained it better this time...

    There may be a better way to represent the data then the x/y axis grid I laid out so I'm open to suggestions on that too.

    -Michael
    Attached Files Attached Files

  4. #4
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,864

    Re: reconcile gift card transactions across multiple locations

    Try F9=IFERROR(INDEX(redemption,MATCH(F$8&$E9,INDEX($C$2:$C$148&$B$2:$B$148,),0)),"") drag down and over the cells.

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

  5. #5
    Registered User
    Join Date
    01-02-2016
    Location
    NJ, USA
    MS-Off Ver
    15
    Posts
    5

    Re: reconcile gift card transactions across multiple locations

    That is close but if there are multiple values that match the criteria it only returns the first value and does not combine them.
    Is there any way of doing that?
    Thank you.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,753

    Re: reconcile gift card transactions across multiple locations

    What about this in F9:R21,
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    or perhaps better yet
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 01-03-2016 at 01:25 AM. Reason: afterthoughts
    Dave

  7. #7
    Registered User
    Join Date
    01-02-2016
    Location
    NJ, USA
    MS-Off Ver
    15
    Posts
    5

    Re: reconcile gift card transactions across multiple locations

    Wonderful!! They both seem to give me the results I'm looking for.
    Thank you very much!!
    -Michael

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,753

    Re: reconcile gift card transactions across multiple locations

    They both seem to give me the results I'm looking for.
    To which post(s) is that addressed?

  9. #9
    Registered User
    Join Date
    01-02-2016
    Location
    NJ, USA
    MS-Off Ver
    15
    Posts
    5

    Re: reconcile gift card transactions across multiple locations

    FlameRetired,
    Both of your formulas gave me the result I was looking for. I used the second one for it's "simplicity" as it showed me what was owed to each location. I then swapped the 'issue' and 'redempt' to get what each location owes and that should be the last piece of my puzzle.
    Thank you for your help!
    -Michael

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,753

    Re: reconcile gift card transactions across multiple locations

    You are welcome. Glad it worked, and thank you for the feedback.

+ 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. Creating a "simple" gift card tracking system
    By MBRooksy1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-20-2015, 12:49 AM
  2. Gift card inventory & invoicing
    By meesha26 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-27-2014, 09:21 AM
  3. Conjoint Market Simulator - $10 Amazon gift card
    By abduljaleel.mca in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2013, 11:25 AM
  4. Replies: 22
    Last Post: 09-20-2013, 02:00 PM
  5. Replies: 2
    Last Post: 09-17-2013, 07:12 PM
  6. Macro to hide rows - Amazon gift card to whoever can help!
    By reubank in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-22-2013, 06:29 AM
  7. Reconcile company credit card bank deposits to books
    By Sheepdog in forum Excel General
    Replies: 2
    Last Post: 10-18-2012, 11:03 AM

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