1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel data extraction

Discussion in 'Business Applications' started by ozzypete, Jul 1, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. ozzypete

    ozzypete Thread Starter

    Joined:
    Jul 1, 2012
    Messages:
    2
    Hi - I'm having difficulty recording a macro to extract a row of data from a large excel file. The file is a csv download and is 160 columns wide and 55000 rows long. I can use ctrl-f to find a record for manual manipulation, but I need to be able to extract a row of data to another location where I can use simple formulae to present the csv data in a meaningful way. In fact there are 2 databases that I need to perform similar operations on to be able to compare side-by-side, but the 160x55000 is the biggest.
    On sheet1 I have a cell location (say b6) into which I want to enter the search criteria. Sheet 2 has 2 rows of headings followed by the csv data range. Ideally I would like to search on different criteria, as the individual records include Ac no, Ref no, and name, but I'm happy to use different search boxes & macros for each type of search rather than making the macro too complicated. It would be great if the macro could locate the row of data and copy it to a fixed area which would then populate the viewing area on sheet 1 with simple formulae, which I can handle ok.
    The csv file has no headers or page breaks (below the top 2 rows) & all the search criteria are contained in the first 8 columns so (I don't know if it's relevant) searching column A first (then b, c etc) would be quicker (I assume) than the ctrl-f cell-by-cell search. I hope I've explained adequately & any help would be very much appreciated - thank you.
     
  2. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    It will be clearer if you can attach a sample file.

    1. do you have duplicates in your source?
    2. do you want to select the row for editing or is it necessary to extract to then compare?
     
  3. ozzypete

    ozzypete Thread Starter

    Joined:
    Jul 1, 2012
    Messages:
    2
    Hi - unfortunately privacy rules prevent me sending data.
    There should be no duplicates in the source file - until I get to searching by name, which is not a priority. I don't want to edit the data - I just need to extract it for comparison purposes. It may help to explain my manual method.....I'm in sheet 2....I use ctrl-f to locate the record I need, then [end] left to make sure I am at the beginning of the record in column a, then I anchor the point and do [end] right to highlight the whole record. Then I do ctrl-c, [end] down, [end] down, ctrl-v to copy the record into the last row of the sheet. From there I can use simple fomulae to populate a summary sheet at the top of sheet 1. Naturally I need to clear the data out again manually from the bottom row to retain the formulae function. Hope this helps & thanks again!
    Regards,
    Peter.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1059245