Results 1 to 2 of 2

Thread: Macro to allocate line item data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member vettetax's Avatar
    Join Date
    Jul 2006
    Location
    Phoenix, AZ
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to allocate line item data

    My 'Data sheet' contains a combination of values on each row. Certain rows require special allocations, the combination of which is referenced on my 'Allocations' sheet. Certain rows are fine and should not be allocated, but merely left in its virgin state and copied over to the 'Revised Data' sheet. The revised allocated data should then be appropriately referenced on the 'Revised Data' sheet.

    Appreciate any help and thank you!


    Data Sheet
    test1 Combo1 Label1 Label2 Label3 Label4 Value
    2 Cat/Dog/House Cat Dog House Combo 100 <----value needs to be allocated and Label4 value re-labeled
    Cat/Dog/Bird Cat Dog Bird Combo 100 <----control data, no impact as Combo1 value is not on the Allocations tab

    Allocation Sheet
    Combo1 Merlin Halo Max
    Cat/Dog/House 25% 25% 50%

    Revised Data sheet
    Label1 Label2 Label3 Label4 Allocated Value
    Cat Dog House Merlin 25 <----Combo1 value respectively allocated and Label4 value re-labeled
    Cat Dog House Halo 25 <----Combo1 value respectively allocated and Label4 value re-labeled
    Cat Dog House Max 50 <----Combo1 value respectively allocated and Label4 value re-labeled
    Cat Dog Bird Combo 100 <----control data, no impact as Combo1 value is not on the Allocations tab
    Last edited by vettetax; Oct 7th, 2019 at 06:33 PM.

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to allocate line item data

    If you were to unpivot your allocation sheet, you could setup a sql left join of your data and allocation sheets using MS Query in excel. Then with a simple formula in the resulting table, your could manage your allocation.

    This page explains how it can be accomplished. It uses an addin to simplify the ms query sql left join stuff, but the premise is there.
    http://www.iwishexcel.com/authors-fa...ble-left-join/
    My favorite Excel Add-in:= Nutilities

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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