+ Reply to Thread
Results 1 to 6 of 6

Monthly Reports

  1. #1
    Registered User
    Join Date
    04-28-2014
    Location
    Rancho Cucamonga, CA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Monthly Reports

    I have a spreadsheet for tracking sales of two companies. There are approximately 1,000 rows of customers. For several reasons we only receive business from some of the clients each month. For instance from one company there is only 135 customers for the month of April. Each month I have to manually match up the 135 companies with the list of 1,000 customers by dragging them to the appropriate place or use insert cells until they all match. I have two colums - company and individual client, then three columns per month - Orders, Qty, Amount. Is there a way to automate matching up the lists? Here is an example:


    Company A Company A
    Company B Company D
    Company C Company F
    Company D Company H
    Company E
    Company F
    Company G
    Company H

    I want it to look like this:

    Company A Company A
    Company B
    Company C
    Company D Company D
    Company E
    Company F Company F
    Company G
    Company H Company H

    Thanks for your help!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Monthly Reports

    Hi and welcome to the forum

    It might make it easier to offer suggestions if you provided a sample workbook, showing what you have and what you want?

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Monthly Reports

    Try this one

    in B2

    =IFERROR(VLOOKUP(A2,$D$2:$D$9,1,0),"")

    A
    B
    C
    D
    1
    List 1 Lookup List 2
    2
    Company A Company A Company A
    3
    Company B Company D
    4
    Company C Company F
    5
    Company D Company D Company H
    6
    Company E
    7
    Company F Company F
    8
    Company G
    9
    Company H Company H
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Registered User
    Join Date
    04-28-2014
    Location
    Rancho Cucamonga, CA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Monthly Reports

    Thank you Alkey, that is useful, but I think FDibbins is right, so I've attached the sample. So what I would do is match April's info with the primary database, then delete the company and adjuster columnsfrom April.
    Attached Files Attached Files
    Last edited by shadey4; 04-30-2014 at 12:10 PM.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Monthly Reports

    I don't think I understand what exactly you are trying to do.

  6. #6
    Registered User
    Join Date
    04-28-2014
    Location
    Rancho Cucamonga, CA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Monthly Reports

    I'm uploading the spreadsheet again with three sheets. The first is the original I posted, the second is the step I'm asking you to do and the third step represents what I will do after I've matched up April's data with the main spreadsheet. Thanks for your help.
    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. Monthly reports
    By BRIZZLE101 in forum Excel General
    Replies: 1
    Last Post: 03-22-2013, 09:42 AM
  2. setting a variable for monthly reports
    By xlsmiller in forum Excel General
    Replies: 1
    Last Post: 05-01-2012, 02:49 PM
  3. Monthly reports from Quarterly Bills
    By naigy in forum Excel General
    Replies: 1
    Last Post: 05-31-2011, 01:22 PM
  4. Turning Daily Reports into Monthly Reports
    By jambezi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-19-2009, 05:31 PM
  5. Macro Updating Monthly Reports
    By blk91rs in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-08-2009, 04:49 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