Extract Data in specific cells into a new worksheet using any method

[ExcellingOS]

Hello,

I need some major help, I don't know where to begin but based on my excel experience there wouldn't be a way for me to develop something myself that extracts the specific data into a new worksheet.

I also would need the first 3 cells to be a row in separate columns so it would appear as, adding the headers isn't necessary I can manually add the headers each time.

Cell A Cell B Cell C
Name Time1 Time2
Jane Doe 10 23

I've attached the file. Example of the cells I need which follow a pattern that begin at row 6 and end at row 360 are:
B6, D8, D9
B10, D12, D13


I could strip the first 5 rows and make it start from 1 to 360, just let me know.

[ExcellingOS]

Actually I would be okay with a solution that creates a fill pattern using

B6, D8, D9
B10, D12, D13

so the third row would have the following in the cells separated by commas
B14, D16, D17

[jaslake]

Hi ExcellingOS

Your sample file doesn't tell me much. What's in those "ignore" cells...please do this...

Please attach a sample file that represents what you actually have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" worksheet that demonstrates what you wish the output to be.

[ExcellingOS]

Hi John,

I've uploaded the files you've requested

time1 = the original file to work with

time2 = the file with the extracted data i need

Thanks so much!

[jaslake]

Hi ExcellingOS

Here's but one convoluted way to do as you require

Please Login or Register  to view this content.

[ExcellingOS]

How would that work with this file that I've attached. Note: dept has been changed

[jaslake]

Hi ExcellingOS

It won't...part of the Code will need revised. Does your most recent attachment TRULY represent your actual File? Do ALL Department designations contain a "-" (dash)? Do the Department designations ACTUALLY look like this:
089100-asa
083200-Marketing
089100-asa
082100-Fundraising
040600-vrc

[ExcellingOS]

Food for thought,

What if an employee leaves? I'm a programmar myself but definitely not a excel guru so I was able to read your code and understand that it was going to fail for the real version. I have several departments, more than 4. For example, one that I didn't include was 020400-Hrc. What would it take for me to add that department to your code?

strValueToPick = "089100", "083200", "etc"?

The other problem that I forsee is I actually have 100 employees and I only listed a few to save time. Will the script support that many rows... kind of like the IF DO WHILE EOF?

[jaslake]

Hi ExcellingOS

What I envision is a Dynamic Named Range of Unique Departments and looping through that Named Range to assign the "strValueToPick". Now, the Code can create the Dynamic Named Range of Unique Departments or you can create it in a separate Worksheets called "Departments"...whichever you desire.

If you wish the Code to create it I need the answer to this

Do ALL Department designations contain a "-" (dash)? Do the Department designations ACTUALLY look like this:
089100-asa
083200-Marketing
089100-asa
082100-Fundraising
040600-vrc

If you wish to create the Dynamic Named Range of Unique Departments then create the list in a separate worksheet called "Departments" and repost your File.

This is not an issue

What if an employee leaves?

The Code will not be looking for Employees; it'll be looking for your Department designations.

This is not an issue unless the File contains more rows of Data than AutoFilter will support (10,000 unique items in Excel 2007).

The other problem that I forsee is I actually have 100 employees and I only listed a few to save time. Will the script support that many rows

[ExcellingOS]

I've sent you a private message. Please let me know here if you need anything else

[jaslake]

Hi ExcellingOS

This Code in in the attached. The approach I've taken is to have the Code create the Department List.

I assumed the answer is Yes to this question

Do ALL Department designations contain a "-" (dash)?

Let me know of issues.

Please Login or Register  to view this content.

[ExcellingOS]

Please review this file.


I know that the worksheet name doesn't say "Time" but I can fix that piece by simply renaming the ws in excel or modifying it in your code ( that part was the easiest thing for me to understand from all the code you wrote ) So its once you get beyond it reading the ws name.

[jaslake]

Hi ExcellingOS

There's a pattern change in the File...see jpg attached. Not sure how to deal with this...will there be other pattern changes?

Pattern Change.jpg

[ExcellingOS]

Hi ExcellingOS

There's a pattern change in the File...see jpg attached. Not sure how to deal with this...will there be other pattern changes?

Attachment 251553

That's weird never noticed that, however if it makes it easier for you I can copy the whole spreadsheet into a new spreadsheet / file and it should clear it up. However, no the pattern will remain the same moving forward for X of years that I can't predict...

[jaslake]

Hi ExcellingOS

If you wish for the Code to be consistent it'll have to deal with anomolies...so...patterns need to be consistent or the inconsistencies (anomalies) dealt with or the Code will not work.

This Code works (for the most part, except the anomalies in the file) on your most recent upload.

Please Login or Register  to view this content.

[ExcellingOS]

Question, why were there 5 or 4 that failed? I see the first space is filled with a space and then the others are either 0.00 or -126?

Actually, it looks like there's a problem with row 69-72, I haven't reviewed the others but it get the correct rows.

[jaslake]

Hi ExcellingOS

In the attached I've added this Code to "fix" the anomaly we discussed. The Code fixes ONLY this specific anomaly. If you have others, Code will need written to address those. I've also made modifications to the Sub Test. The Code assumes your Raw Data Worksheet is the first worksheet in the Workbook. Let me know of issues...

Please Login or Register  to view this content.

[ExcellingOS]

Fantastic job, I'll mark this solved in a bit. Just reviewing a few things.

[jaslake]

Hi ExcellingOS

How did you resolve the CSV issue?

[ExcellingOS]

Hi ExcellingOS

How did you resolve the CSV issue?

Initially before receiving the file from my HR dept, I was working on getting the table ready so it didn't occur to me there would be decimals at all so I had used a integer data type. So the issue was the table was designed not to support decimals at all so it wouldn't let the csv file run unless those 2 columns had no decimals.

This has been solved. Excellent job jaslake, let me know how I can repay you. I've already added reputation to your last post.

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.

[ExcellingOS]

Sorry to reopen this case:

Hi Jaslake,

I've discovered an issue I was going to encounter as I progressed with this project that I'm working on. I'll need another column added to the code if you can help me with this. The column will be called adpnum, it's going to extract the cells in A7, A10. The code would be nice if it could be expanded to support any kind of digits in case the file started at the 90000 but that may be for a few years from now so it may not be an issue atm.

I've uploaded the file with your code, the another file of exactly of what im looking for. Also, I didn't mention anything last time about this but the code you've wrote creates a blank 2nd row... is it possible to get rid of that? if not its not a big deal i can delete the row every time.

Thanks again

[chakravarthysm]

thank u very much,i got the solution....,

[jaslake]

Hi ExcellingOS

This was not as involved as I thought it would be. Had to move a line of Code, modify a line or 3 and add a line or 3. See if it works for you.

PS: Sorry, I missed this

creates a blank 2nd row... is it possible to get rid of that

Add this line of Code to the Sub Test(), way at the bottom

Please Login or Register  to view this content.

[ExcellingOS]

Perfect, your efforts is GREATLY appreciated.

Thanks, solved and repped.

[jaslake]

Hi ExcellingOS

See the PS: to my Post #24...simply add the indicated line of Code...it'll get rid of the blank line.

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.