Results 1 to 1 of 1
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Extracting numbers and data

    I have a spreadsheet containing account numbers in Col F and value in column H

    I would like VBA to extract the account numbers from Audit Trail Sample (Source Workbook) and to copy these in the respoective sheets as follows in the destinatination workbook

    A) sheet 2215B

    1) Where 2215B appears in Col F, I want the number above 2215B including 2215B to be extracted as well as the values two columns to the right of it, excluding the following numbers in Col F 6900, 1102, 2310, 2311, 2316, 2200 and the values one column to the right i.e col H
    2) Once the numbers and values have been extracted to sheet 2215B, I want the values the are directly above 2215B added and multiplied by 0.14 to ensure that this agrees with the value two columns to the right of 2215B i.e in Col H-I have manually extracted some data to show you what is required
    3) Where 2218B appears in Col F, this account and the accounts and values two columns to the right and directly above it are to be ignored
    4) Where 2215S appears one row above 2215B , then this can be ignored i.e 2215B and 2215S directly above it not to be extracted

    B) Sheet 2215S

    Where 2215S appears in Col F, I want the number above 2215S including 2215S to be extracted as well as the values two columns to the right of it, excluding the following numbers in Col F 6900, 1102, 2310, 2311, 2316, 2200
    2) Once the numbers and values have been extracted to sheet 2215B, I want the values that are directly above 2215S added and multiplied by 0.14 to ensure that this agrees with the value in 2215B-I have manually extracted some data to show you what is required
    3) Where 2218S appears in Col F, this account and the accounts and values two columns to the right and directly above to be ignored

    See Aduit trail extraction sample of what I extracted manually to give you an idea of what is required

    Your assistance in this regard is most appreciated

    http://www.mrexcel.com/forum/showthr...63#post3189963
    Attached Files Attached Files
    Last edited by HowardC; 2012-06-25 at 13:38.

Posting Permissions

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