+ Reply to Thread
Results 1 to 9 of 9

Sort, subtotal the lookup values from other sheet with adding prefix's

  1. #1
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Unhappy Sort, subtotal the lookup values from other sheet with adding prefix's

    Hello Team,

    attached is the sheet having Input sheet, verify sheet & output sheet. on the basis of below part1,2,3 to perform steps & generate output sheet... please assist how can this be achieved in a button click. thank you

    Part1 - copy all input sheet data to output sheet

    Now IN Output Sheet
    Sort table from column A to Column V on the basis of Column F
    SUbtotal on the basis of Column F & the total will be calculated for Column H

    Part2 - IN Output Sheet

    IF Column V = "ONE" then convert value of column H will (+)
    IF COlumn V = "ONE-Door" then convert value of column H will (-)
    IF COlumn V = "TWO" then convert value of column H will (-)
    IF COlumn V = "TWO-Door" then convert value of column H will (+)

    Part3 - VLOOKUP FROM SHEET "VERIFY" & plot data in column I & J ("Output") sheet

    Compare subtotal value in colulm F of output sheet with column B of verify sheet & plot On & VAL data
    ex: - "1A total" should be matched with 1A of verify sheet
    Attached Files Attached Files
    Last edited by Parth007; 08-10-2015 at 06:31 AM.
    Regards
    Parth

    I appreciate your feedback. Hit * if u Like.
    Rules - http://www.excelforum.com/forum-rule...rum-rules.html

  2. #2
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Sort, subtotal the lookup values from other sheet with adding prefix's

    Till now i have done with this simple code..

    I have a Command button which calls all the bbelow three codes..

    Please Login or Register  to view this content.
    1) Clear Output1 sheet for fresh data & copy Input data & Paste in Output1 sheet
    Please Login or Register  to view this content.
    2) Sort Column F in Output1 sheet
    Please Login or Register  to view this content.
    3) Applying Subtotals on Column F & dispaly values for Column H
    Please Login or Register  to view this content.
    4) Finally Moving only data to Output sheet
    Please Login or Register  to view this content.
    But Actually it shows subtotalas & not a clear file as the "Required_Output" sheet.

    Moreover i need to vlookup values from "Verify" Sheet in the output sheet...

    Please suggest


    Part 2 & part 3 is left.. puzzled..

    please see the attached sheet
    Attached Files Attached Files
    Last edited by Parth007; 08-04-2015 at 09:38 AM. Reason: Updated the code..

  3. #3
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Sort, subtotal the lookup values from other sheet with adding prefix's

    How do i update Column H with the (+) & (-) sign as prefix on the basis of column V (Part 2 Conditions) & part 3

  4. #4
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Sort, subtotal the lookup values from other sheet with adding prefix's

    no code found in google similar




  5. #5
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Sort, subtotal the lookup values from other sheet with adding prefix's

    I Mean,

    Part2 - IN Output Sheet
    IF Column V = "ONE" then convert value of column H will (+)
    Example: - IF Column H have Cell.value (-54) & if the Column V have Cell.value = "ONE" then column H value be (+54)

    similarly for below
    IF COlumn V = "ONE-Door" then convert value of column H will (-)
    IF COlumn V = "TWO" then convert value of column H will (-)
    IF COlumn V = "TWO-Door" then convert value of column H will (+)





    Part3 - VLOOKUP FROM SHEET "VERIFY" & plot data in column I & J ("Output") sheet
    Compare subtotal value in colulm F of output sheet with column B of verify sheet & plot On & VAL data
    ex: - "1A total" should be matched with 1A of verify sheet

  6. #6
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Sort, subtotal the lookup values from other sheet with adding prefix's




  7. #7
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Sort, subtotal the lookup values from other sheet with adding prefix's

    Hi, try out this code..

    Notice I'v hard coded ranges in some places.. Change it to dynamic ranges if required..

    Also make it a point to maintain your sheets properly without any Blank Rows/Columns/Cells. Else it can make the code return some unexpected results.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by NeedForExcel; 08-10-2015 at 06:21 AM.
    Cheers!
    Deep Dave

  8. #8
    Valued Forum Contributor Parth007's Avatar
    Join Date
    12-01-2014
    Location
    Banglore
    MS-Off Ver
    2010
    Posts
    879

    Re: Sort, subtotal the lookup values from other sheet with adding prefix's

    Bingo... on the target.. thanks deep

  9. #9
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Sort, subtotal the lookup values from other sheet with adding prefix's

    Glad I could help..

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. match/sort - lookup data from one sheet and link to other sheet -
    By javaidmr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-11-2015, 10:04 AM
  2. [SOLVED] Adding a prefix to to copied values
    By mosesthetank in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-03-2014, 08:57 AM
  3. Replies: 1
    Last Post: 08-11-2012, 12:30 PM
  4. Adding prefix to a name
    By [email protected] in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-28-2010, 03:08 AM
  5. Using a macro to sort and subtotal, but different number of lines on each sheet
    By kafarrell in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 06-21-2010, 05:26 PM
  6. Sort by subtotal, make the subtotal stand out
    By CC_mfc in forum Excel General
    Replies: 12
    Last Post: 02-12-2007, 11:06 AM
  7. Adding zero prefix to no's
    By Cheryl W in forum Excel General
    Replies: 4
    Last Post: 05-30-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