+ Reply to Thread
Results 1 to 5 of 5

Report with grouped/sub-totaled by vendor - Need vendor name and number on each txn

  1. #1
    Registered User
    Join Date
    04-21-2017
    Location
    USA
    MS-Off Ver
    Excel, Word, PowerPoint
    Posts
    2

    Report with grouped/sub-totaled by vendor - Need vendor name and number on each txn

    Good afternoon,

    We got a vendor transaction report from the system that groups and sub-totals the payment data by vendor. However, we want to analyze each vendor payment transaction and would like the vendor name and number that's summarized at the top of each section to be copied as a column for each transaction. Please see sample file attached.

    If you can't open the file, below is an example of the problem:

    What the file looks like currently:

    Vendor # 123 Vendor Name ABC
    Invoice # Invoice Amount $ Date Payment Amount $
    A123 $10,000 1/1/2016 $10,000
    B123 $5,000 1/7/2016 $5,000

    Vendor # 567 Vendor Name DEF
    Invoice # Invoice Amount $ Date Payment Amount $
    C123 $10,000 1/10/2016 $10,000
    D123 $5,000 1/15/2016 $5,000
    E123 $10,000 1/10/2016 $10,000
    F123 $5,000 1/15/2016 $5,000

    What I want the data to look like (without having to go one by one as the data file is thousands of records long):

    Invoice # Invoice Amount $ Date Payment Amount $ Vendor # Vendor Name
    A123 $10,000 1/1/2016 $10,000 123 ABC
    B123 $5,000 1/7/2016 $5,000 123 ABC
    C123 $10,000 1/10/2016 $10,000 567 DEF
    D123 $5,000 1/15/2016 $5,000 567 DEF
    E123 $10,000 1/10/2016 $10,000 567 DEF
    F123 $5,000 1/15/2016 $5,000 567 DEF

    I tried a vlookup formula but it would need to be adjusted for each grouping since each has a different # of rows within it.

    Your help is much appreciated.
    Thank you
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Report with grouped/sub-totaled by vendor - Need vendor name and number on each txn

    In your file, is the Payment amount, column C? Is the voucher, the same as Invoice Number?
    If not, please clarify.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Report with grouped/sub-totaled by vendor - Need vendor name and number on each txn

    i did logic and left you INDEX
    Attached Files Attached Files
    Last edited by tim201110; 04-21-2017 at 02:35 PM. Reason: better calculations

  4. #4
    Registered User
    Join Date
    04-21-2017
    Location
    USA
    MS-Off Ver
    Excel, Word, PowerPoint
    Posts
    2
    Quote Originally Posted by ChemistB View Post
    In your file, is the Payment amount, column C? Is the voucher, the same as Invoice Number?
    If not, please clarify.
    I apologize the payment amount is not included in the file example. The invoice number is column C. What I'm looking to do is add the corresponding vendor number and vendor name at the end of each row. Hopefully that helps

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Report with grouped/sub-totaled by vendor - Need vendor name and number on each txn

    Based on your example, I used this formula

    =IF(LEN(A4)>16,OFFSET($A$1,LOOKUP(2, 1/($A$1:$A4="Voucher"),ROW($A$1:$A4))-2,0),"")
    For Vender name, I used
    =IF(LEN(A4)>16,OFFSET($A$1,LOOKUP(2, 1/($A$1:$A4="Voucher"),ROW($A$1:$A4))-2,1),"")

    Is that what you need?
    Attached Files Attached Files

+ 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. Same bid vendor
    By SusanWi in forum Excel General
    Replies: 4
    Last Post: 12-13-2015, 01:26 AM
  2. Compare/Match values by certain criteria (Vendor Number and System)
    By Alx77 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-10-2013, 02:01 PM
  3. Vendor Number Next To Each Invoice Number
    By kusidin in forum Excel General
    Replies: 4
    Last Post: 11-04-2012, 05:58 AM
  4. Vendor Number Next To Each Invoice Number
    By kusidin in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-04-2012, 05:55 AM
  5. Vendor Number Next To Each Invoice Number
    By kusidin in forum Excel General
    Replies: 2
    Last Post: 11-04-2012, 05:23 AM
  6. Excel 2008 : Assign Vendor to Item Number
    By DADADO in forum Excel General
    Replies: 2
    Last Post: 07-21-2012, 03:04 AM
  7. Accounting - Vendor Detail Report
    By GarryS in forum Excel General
    Replies: 2
    Last Post: 01-18-2010, 06:38 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