+ Reply to Thread
Results 1 to 16 of 16

Help with a macro to extract specific data from large workbook, extensive details inside.

  1. #1
    Registered User
    Join Date
    07-10-2012
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    10

    Help with a macro to extract specific data from large workbook, extensive details inside.

    Hi everyone, new to the community, but it seems like the best place to ask for help on the web.

    Hope someone here can help me out, or at least point me in the right direction of a similar example.

    I believe the programming logic I wrote is sound. I’m using Excel 2003, not sure how this effects my situation but I have pretty much lost all of my VB syntax knowledge.

    Source material is a large workbook with tons of data and multiple sheets; I just want to create a single sheet having each SKU associated with a specific cost. I’m pretty sure a macro would be the best way to approach this, as sometimes the Row location varies based on the sheet. The SKUs are always in Row 1, but sometimes the Row labeled "Net Landed Cost" can be in a different row (However, it's always in Column A).

    I'm not sure how time-consuming this macro would take to write but I think it would be pretty simple for someone who is as familiar with the syntax as I used to be.

    Here is what I need to do:


    1. Create a new workbook, or sheet if it is easier. We’ll call it “Z”
    • a. Label A1 = “SKU”
    • b. Label B1 = “Net Landed Cost”

    2. For each sheet “N” in workbook search Column A for the first occurrence of “Net Landed Cost” to determine the Row “X” we will be extrapolating data from. When there are no more sheets “N” remaining, the macro stops.

    3. I suppose I need to make a loop “n” to move across Row(“X”) horizontally starting at column B (as column A will be labeled “Net Landed Cost”)
    1. IF Cell at (Column location n in loop, Row(“X”)) has a value greater than or equal to 0, copy the data from this cell into sheet “Z”, to the next available empty row in Column B. This is the cost I need. Then copy the data from Cell=(Column location n in loop, row 1), into sheet “Z”, to the next available empty row in Column A. (These should both be in the same row, eg Matching SKUs with the cost).
    2. IF Cell at (Column location n in loop, Row(“X”)) is blank, move to next column)
    3. WHEN no further columns in Row(“X”) have data (e.g. end of the worksheet horizontally), move to the next sheet “N”.


    4. Here are some concerns I have that may or may not be issues in writing this macro:
    • Ensuring that the Row “X” is determined by the first instance of “Net Landed Cost” as “Net Landed Cost” will appear multiple times in Column A of each sheet.
    • Row “X” (which has my costs), is not consistent between sheets, however my SKUs will always be in the cell that is the first Row in the column that we are on in the Loop. (e.g. if Row “X” is 11, And we are at n=Column AB in the loop, the Cost will be in CELL(AB11) and the SKU will be in CELL(AB1).
    • Ensuring that the loop moves on to the next sheet after completing Row “X”. I do not want the data from further occurrences of “Net Landed Cost,” or I will not be able to attain the correct information.
    • Ensuring that the loop for Row “X” moves across columns all the way to the last column with data, sometimes there are multiple blank columns in a row.
    • Ensuring that when copying data over to sheet “Z” (place 2>b>i in outline), both SKU and cost are copied to the same row, and that the loop then properly moves to the next row.
    Last edited by madmax405; 07-10-2012 at 05:29 PM. Reason: formatting

  2. #2
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Help with a macro to extract specific data from large workbook, extensive details insi

    Can you make a small copy of the workbook?

  3. #3
    Registered User
    Join Date
    07-10-2012
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    10

    Cool Re: Help with a macro to extract specific data from large workbook, extensive details insi

    MACRO TESTING.xls

    I think I got this attachment to work.
    Hope you can help me out!

    The workbook is six sheets.
    First sheet is what I would like to see the macro create based on the sheets labeled Test 1 thru Test 5
    I put notes in column A of each Test sheet to describe to you what I did, what I'm looking for, and what changes in between tests to make sure exceptions are accounted for.

    Thanks so much!

    Max

  4. #4
    Registered User
    Join Date
    07-10-2012
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Help with a macro to extract specific data from large workbook, extensive details insi

    Ok I got it posted, so check the attachment and let me know what you come up with

  5. #5
    Registered User
    Join Date
    07-10-2012
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Help with a macro to extract specific data from large workbook, extensive details insi

    fredlo, you still taking a shot at this?

    Or, anyone else have some answers for me? (example workbook is attached in replies, request in OP)

    It's appreciated more than you know

    -max

  6. #6
    Registered User
    Join Date
    07-10-2012
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Help with a macro to extract specific data from large workbook, extensive details insi

    Bumping because surely there is someone out there who can at least point me in the right direction?
    If I can get this done by the morning, I can save four hours tomorrow alone, and I have to do something similar multiple times a week; that's hardly an exaggeration
    I've been looking for hours and I'm completely lost, and surely someone could at least point me in the right direction if they couldn't help me with the actual writing I'm more than willing to try to tackle it myself... especially if they already knew where a similar example is, because I can't find it for sure!
    I haven't touched VB in about 10 years, and I honestly would bet an adept macro writer could do this in 10 minutes...

    Appreciate any responses,
    maximus
    I'll thank you any way I can!
    Even call on your birthday and leave you a message of me singing happy birthday on your answering machine :P

  7. #7
    Registered User
    Join Date
    07-10-2012
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Help with a macro to extract specific data from large workbook, extensive details insi

    morning bumpage, still need macro help

    example workbook attached in one of the replies above, original info is in OP

    -max

  8. #8
    Registered User
    Join Date
    07-10-2012
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Help with a macro to extract specific data from large workbook, extensive details insi

    evening bumpage
    doing this manually meant I didn't get off until 7 today, I'm so lost

    someone must have a similar macro or something to even get me started in the right direction, please, anyone?

  9. #9
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Help with a macro to extract specific data from large workbook, extensive details insi

    Hi Max

    I am sorry for the delay I have been super busy.

    See if this code works.

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    07-10-2012
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Help with a macro to extract specific data from large workbook, extensive details insi

    Thanks so much I was so excited to see your reply, been sitting here four hours trying to do it myself and got nowhere :P Honestly can't thank you enough

    Awesome it seems to be working as intended based on my test sheet, for some reason my remote login is not working properly right now so I cannot test it against the real thing, so going to wait to mark it solved until I test firs thing in the morning.

    I also have a few quick scenarios I was wondering if you have a quick fix for.
    1 is important to me, 2 and 3 are not so much, but if they are easy, it's much appreciated.

    1. Let's pretend I merge any of the SKU cells (this happens a few times on the workbook if it's the same SKU at a different Cost because of vendor, etc).
    For example I go to TEST 1 Sheet and Merge D1 (SKU 10003) and E1 (SKU 10004). We have two costs associated with this one SKU now (10003), $15 and $20.

    SKU 10001 10002 10003 10005
    COST 5 10 15 20 25


    Testing this on the macro will not return any values period, it now omits both costs and doesn't list the SKU, this is the result:

    10001 $5
    10002 $10
    10005 $25

    Ideally, I would like it to return

    10001 $5
    10002 $10
    10003 $15
    10003 $20
    10005 $25

    Does this make sense?

    Or any other ways of tackling merged cells?

    2. I notice if I run the macro twice it doesn't erase the sheet, it just continues... can you have it blank out the sheet every time the macro is run? (If this is more than a simple command, I'll just do it manually)

    3. Is it a simple command to set A1 to "SKU" and B1 to "Net Landed Cost" to the "Ideal Macro Output" sheet and start the data entries on row 2 instead of row 1? (Again, if it's more than a simple command, I'll just insert the row manually.)

    EDIT:

    I've tried the "Cells.MergeCells = False" command, however, this returns

    10001 $5
    10002 $10
    10003 $15
    SKU MISSING $20
    10005 $25

    because it will remove the data from cell E1. Is there a followup command to copy the data from every cell unmerged to each cell that was originally merged?
    EG I run Cells.MergeCells = False and it unmerges D1 and E1
    Then How do I tell it to copy over to the cells that were unmerged? It's not like it's always going to be in the same direction.

    Only need to unmerge cells in row 1 btw, but across all sheets with one button would be nice.
    Last edited by madmax405; 07-12-2012 at 01:53 AM.

  11. #11
    Registered User
    Join Date
    07-10-2012
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Help with a macro to extract specific data from large workbook, extensive details insi

    I took care of it for the most part... I had to do it in a separate module but thats no big deal... thanks again so much, will report in morning

  12. #12
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Help with a macro to extract specific data from large workbook, extensive details insi

    Hi,

    How about this I think everything is covered

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    07-10-2012
    Location
    NJ
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Help with a macro to extract specific data from large workbook, extensive details insi

    you have been super helpful, i've been ultra busy but will post again later, but THANKS!!!

  14. #14
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Help with a macro to extract specific data from large workbook, extensive details insi

    I am glad I helped you out

  15. #15
    Registered User
    Join Date
    07-15-2012
    Location
    US
    MS-Off Ver
    Excel 2007/10
    Posts
    4

    Re: Data Extract Macro

    Hi guys,

    I have similar problem but Im not a macro expert. Pls have a loook at my post.

    http://www.excelforum.com/excel-prog...her-files.html

    Thanks a lot!
    Attached Files Attached Files
    Last edited by harsh767; 07-15-2012 at 02:39 PM.

  16. #16
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Help with a macro to extract specific data from large workbook, extensive details insi

    Hello harsh767, and welcome to the forum. Unfortunately you've inadvertently broken one of the forum rules. Please read the following.
    Thanks

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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