+ Reply to Thread
Results 1 to 5 of 5

Vba to lookup for specif alphanumeric value in a column and pull value from adjacent cell

  1. #1
    Forum Contributor
    Join Date
    11-17-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    365

    Vba to lookup for specif alphanumeric value in a column and pull value from adjacent cell

    I have a Data in Sheet "Offline"
    in Column "D" I have Numbers and Alpha Numeric Numbers.(Heading "UTR")
    Some cells Single Might contains single numeric or alphanumeric number
    Some cells Single Might contains multiple Numeric and Alpha Numeric Numbers separated with"/"

    In sheet "UTR"
    I have list of only single numeric or alphanumeric number in column "I" and have some amount against that in Column "J"
    Some cells in Column "I" Might contain "#N/A" or "N/A" or empty. Please ignore of it has any these three.

    Task:
    Need VBA to perform following task. Please find attached.
    1. On sheet offline, from Column "D" split/extract the contents by "/" (just like text to column). The extraction should start from column G onwards.
    2. Then check for maximum number of split from single cell.
    3. Give Heading to all splited column as (UTR-1, UTR-2, UTR-3 etc. depend upon the number of splits). For example: if D2 contains 2 UTR and D3 contains 7 UTR then D3 should be splited to 7 columns. i.e. Column G to Column M and name G1 as UTR-1 , H1 as UTR-2, I1 as UTR-1 and so on.
    4. Insert One column after every extracted UTR column and Name it as AMT-1, AMT-2, AMT-3 etc.
    5. Now I want to lookup for UTR No. from sheet “Offline” to column “I” of sheet “UTR”. IF UTR exists in column “I” then pull the amount against the UTR and save it against the matching UTR in Column “UTR-1”. In same way I wants to do it for all columns.
    6. After that I wants to Insert two Column at the end “TOTAL Number of UTR “ and “TOTAL AMT” . In “TOTAL Number of UTR” I want the count of total UTR extracted from Column G onwards and in column “TOTAL AMT” I want the sum of all amount pulled against all UTR. In sheet off line I have marked desired output in green colour and result in yellow colour.

    Please note

    1. In column D of sheet “Offline: the separator “/” of UTR can be any number. Hence have insert UTR-1, UTR-2 etc till it reached the last separation.

    2. The Code should run in sheet “offline” for those records till the end of Sr. No. (Column A)
    Attached Files Attached Files
    Last edited by saravanan1981; 12-01-2015 at 09:59 PM. Reason: NOT AS PER FORUM RLES

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: vba to extract, look up, count and sum

    title updated

    It seems you need a complete vba solution --> Commercial Services is an option.
    Last edited by protonLeah; 12-02-2015 at 01:54 AM.
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    11-17-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    365

    Re: Vba to lookup for specif alphanumeric value in a column and pull value from adjacent

    Hi,

    I have changed the title

  4. #4
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Vba to lookup for specif alphanumeric value in a column and pull value from adjacent

    Hi saravanan1981,

    Try the attached file which does the following per your instructions:
    Processing Algorithm:
    a. Sheet Offline (Primary) and Sheet UTR (Secondary).
    b. Delete Columns G thru Last Column
    c. Find Last Row in Column 'A'
    d. Parse Data I nColumn 'D"
    e. Save the Max number of items parsed
    f. Put data in every other column starting at 'G'.
    g. Lookup Data in Sheet UTR and fill in the AMT columns.
    h. Count the total number of UTR values in each row.
    i. Create a formula for Sum of Each AMT column.
    j. Create Headers in Row 1 (Calibri, 11, Bold), ColorIndex = 42 (Blue).
    k. Format data (Calibri 11), borders around each cell. Data Cells ColorIndex = 43 (Green), Last 2 columns ColorIndex = 6 (Yellow)

    Complete Code follows in Ordinary Code Module ModProcessUTR:
    Please Login or Register  to view this content.

    It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:
    Please Login or Register  to view this content.
    This option requires all variables to be declared and will give a compiler error for undeclared variables.

    To enable Macros and to Run Macros see the following:
    http://office.microsoft.com/en-us/ex...010031071.aspx
    http://office.microsoft.com/en-us/ex...010014113.aspx
    If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

    To access Visual Basic (VBA) see:
    http://www.ablebits.com/office-addin...a-macro-excel/
    a. Click on any cell in the Excel Spreadsheet (may not be needed).
    b. ALT-F11 to get to VBA.
    c. CTRL-R to get project explorer (if it isn't already showing).
    d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

    Lewis

  5. #5
    Forum Contributor
    Join Date
    11-17-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    365

    Re: Vba to lookup for specif alphanumeric value in a column and pull value from adjacent

    hi can you please provide code for below thread.
    http://www.excelforum.com/excel-prog...ml#post4285701

+ 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. Extract data greater than a count of 1
    By rwl518p in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-19-2014, 12:55 PM
  2. [SOLVED] Extract data from sheet and count
    By rschoenb in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-19-2014, 04:32 PM
  3. [SOLVED] Invoice count per head per month extract
    By makinmomb in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-09-2014, 11:21 AM
  4. [SOLVED] Ho do I extract just first name and count the most popular names?
    By Nuviman in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 07-21-2014, 02:29 AM
  5. [SOLVED] Extract and count data - macro
    By missy22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-16-2014, 07:07 AM
  6. Extract and count from different columns
    By mnur in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2013, 11:35 PM
  7. How to extract duplicate from the range of cells and gives its count?
    By nagesh_mvj in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-20-2012, 02:48 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