+ Reply to Thread
Results 1 to 10 of 10

Extracting 3 lines per client set based on criteria

  1. #1
    Registered User
    Join Date
    07-21-2015
    Location
    West Midlands
    MS-Off Ver
    2010
    Posts
    43

    Extracting 3 lines per client set based on criteria

    Hi All,

    I have been asked to automate a task which takes someone hours to do on a daily basis, due to the amount of data.

    Attached is a spread sheet (nothing confidential as ran a dummy quote through a website).

    Basically, column A contains the data I get from a website, in the format it is in. I want to extract the name which always starts with "1.", "2." etc, the next line which is the cost and then the line which says "Total:###", so long as the previous line starts with the word compulsory, as there are multiple totals on some data sets.

    I want the data to be copied and pasted/transposed as they look in F1:H6.

    I don't even know where to start with this, any help is greatly appreciated.

    Thanks
    Dustin
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Extracting 3 lines per client set based on criteria

    Do you and colleague have access to PowerQuery/Get & Transform (PQ)? It's free add-in for Excel 2010, comes standard for Excel 2016.
    As far as I'm aware, 2013 requires specific license SKU to use PQ.

    This sort of data transformation can easily be done with that tool.

    If not... you can try following formula solution as well.
    For Name:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For Cost: Since it's always 1 cell below Name... you just add 1 to Row() function.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Then for Total:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    All formula confirmed as array (CTRL + SHFT + ENTER)

    I'll see if I can come up with VBA solution a bit later.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Extracting 3 lines per client set based on criteria

    Here's file with formula applied.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Extracting 3 lines per client set based on criteria

    These formulas work as well. I've tried them.
    Name:[in C2]
    HTML Code: 
    Cost:[in D2]
    HTML Code: 
    XS:
    HTML Code: 
    As stated before all formulas are entered with ctrl+shft+ent.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Extracting 3 lines per client set based on criteria

    Here's version with PQ.

    You can follow each steps applied in the query editor "Applied Steps" pane.

    If more data are added to column A or data is replaced. You can just refresh the query and it will update.
    Attached Files Attached Files

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Extracting 3 lines per client set based on criteria

    Here's how I'd do it using VBA. I assumed Windows based machine.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-21-2015
    Location
    West Midlands
    MS-Off Ver
    2010
    Posts
    43

    Re: Extracting 3 lines per client set based on criteria

    Thank you for the vba version, this will run with hundreds of data sets at once, so it was VBA that would be needed, I will work that into my report and upload an updated version when completed.

    Thank you for all the replies.

  8. #8
    Registered User
    Join Date
    07-21-2015
    Location
    West Midlands
    MS-Off Ver
    2010
    Posts
    43

    Re: Extracting 3 lines per client set based on criteria

    I have tested with my existing code (that gets the data in column A) and it works perfect together, I just have to neaten up and put it in the presentable format I need, if I mark this as solved am I still able to upload the final version at a later point?

    Thank you for the help, I know this would have taken me weeks to figure out in small snippets and would have been mega messy code.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Extracting 3 lines per client set based on criteria

    Here's an alternative, all using non-array (ordinary formulae).
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  10. #10
    Registered User
    Join Date
    07-21-2015
    Location
    West Midlands
    MS-Off Ver
    2010
    Posts
    43

    Re: Extracting 3 lines per client set based on criteria

    Hi Glenn,

    Sorry but it is the VBA I will need to use, what I am essentially doing is populating input boxes online from lines in a spreadsheet (each line is a new run of the VBA) then running the data through the site and then extracting the results into column A, which then means I run the VBA to get the 3 columns stated, paste that into another tab and repeating the process until the VBA finds a blank line, this could have from 10 lines to repeat on or up to 90,000, it can take hours to run when its a lot of data but the VBA works really well now.

    Thank you for the reply though. having the options helps, as I will be creating similar reports for others to use that have macros blocked for security purposes.

+ 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. Extracting dates from a client database
    By aaronbaxter in forum Excel General
    Replies: 1
    Last Post: 01-29-2016, 11:44 AM
  2. Replies: 1
    Last Post: 04-02-2012, 09:11 AM
  3. [SOLVED] removing lines based on a certain criteria
    By im_really_mad in forum Excel General
    Replies: 4
    Last Post: 03-19-2012, 03:22 PM
  4. Extracting lines from a table based on market type
    By prospereau in forum Excel General
    Replies: 2
    Last Post: 08-04-2011, 03:00 AM
  5. Help Extracting Data Based On two Criteria
    By chadha.hitesh in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-09-2010, 11:08 AM
  6. Extracting Data Based on Criteria
    By SJT in forum Excel General
    Replies: 3
    Last Post: 06-16-2007, 04:18 PM
  7. Extracting data from a client worksheet to create an invoice
    By Jacques E. Bouchard in forum Excel General
    Replies: 1
    Last Post: 05-08-2005, 03:06 AM

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