Extract specific data from one sheet to another worksheet

Hi All, please help me in extracting a specific data automatically from the master sheet to another work sheet. I'm zero in macros so wanna do this using formulas if any.

Sample sheet attached.

From the data sheet i want to extract only the certain details meeting specific conditions

Pls help or suggest a better way.

Urgent Requirement!

Thanks in advance !

The current format of your data does not make the analysis of the info very easy since you have the dates going across the columns. If you could restructure your spreadsheet to work vertically as I have sampled in the attached workbook (see sheet: Structure of data), then you can very easily use auto filters to get the required data you need (see sheet: Filter Data).

I don't know if others would be willing to try formulate an expression to do this, but IMHO, it would be very complex (if not impossible) to have formulas extract data for each date and build a list based on this structure you currently have.

I would seriously look into changing the structure (and make the dates run vertically down in columns), to facilitate the extraction of data in the format you need.

Sample.xls

Rudis, good to have your suggestions but the complexity here is we have more number of employees and making their data's repeated for each day doesn't seems good with the master data sheet. pls suggest other ideas to extract specific datas related to permission from the existing format..

Originally Posted by divi123

Rudis, good to have your suggestions but the complexity here is we have more number of employees and making their data's repeated for each day doesn't seems good with the master data sheet. pls suggest other ideas to extract specific datas related to permission from the existing format..

OK...
I've been working on a macro to compile the Extraction.
See the attached workbook.
Save it to your computer and then run the macro called: CreateExtraction. (Press ALT+F8 to see and run the macro)

Sample.xls

Some HEAVY formula's indeed. I commend you for the formula builds. VERY well done!!!

HI Rudis, working it with macro is simple but i never tried it before.. Could you please guide to run this with my original data sheet..

Originally Posted by AZ-XL

Because you use excel 2003 and your table is not well structured for later calculations, functions are very long. I write here most important parts. they are Status and attendance headers.
Attendance:
c2 =IF(ISERROR(SMALL(IF(COUNTIF($I$1:$I$2,'data sheet'!$D$5:$AL$13),'data sheet'!$D$3:$AL$3,""),ROW()-1)),"",SMALL(IF(COUNTIF($I$1:$I$2,'data sheet'!$D$5:$AL$13),'data sheet'!$D$3:$AL$3,""),ROW()-1))

Status
F2=IF(ISERROR(INDEX(INDEX('data sheet'!$D$5:$AL$13,0,MATCH(C2,'data sheet'!$D$3:$AL$3,0)),SMALL(IF(COUNTIF('Extraction (2)'!$I$1:$I$2,INDEX('data sheet'!$D$5:$AL$13,0,MATCH(C2,'data sheet'!$D$3:$AL$3,0)))*ROW($1:$9),COUNTIF('Extraction (2)'!$I$1:$I$2,INDEX('data sheet'!$D$5:$AL$13,0,MATCH(C2,'data sheet'!$D$3:$AL$3,0)))*ROW($1:$9),""),COUNTIF('Extraction (2)'!$C$2:C2,'Extraction (2)'!C2)))),"",INDEX(INDEX('data sheet'!$D$5:$AL$13,0,MATCH(C2,'data sheet'!$D$3:$AL$3,0)),SMALL(IF(COUNTIF('Extraction (2)'!$I$1:$I$2,INDEX('data sheet'!$D$5:$AL$13,0,MATCH(C2,'data sheet'!$D$3:$AL$3,0)))*ROW($1:$9),COUNTIF('Extraction (2)'!$I$1:$I$2,INDEX('data sheet'!$D$5:$AL$13,0,MATCH(C2,'data sheet'!$D$3:$AL$3,0)))*ROW($1:$9),""),COUNTIF('Extraction (2)'!$C$2:C2,'Extraction (2)'!C2))))

Uploading file.

Hi AZ-XL, could you please explain this a little? i find an error when i change the data ranges.

Originally Posted by divi123

HI Rudis, working it with macro is simple but i never tried it before.. Could you please guide to run this with my original data sheet..

Hi,

The macro is built according to the structure of the sample file you uploaded. If the sample file is similar in structure to your original file, the macro will run without problems to create the summary. If your original file is structured differently, then the macro might debug with problems...

To transfer the macro into your original file, follow these steps:

1. Open the sample file with the macro in it.
2. Press ALT+F8 to open the Run Macro dialog
3. Select the CreateExtraction macro in the white window and click on Edit.
4. this will take you to the actual macro code.
5. Copy the entire macro fro Sub to End Sub to the clipboard
6. Open the file that contains your Original Data
7. Press ALT+F11 (this opens the VBA editor)
8. In the VBA editor, click the INSERT Menu at the top of the window, and choose MODULE
9. Paste the code you copied into this new blank module
10. Click the SAVE button on the VBA editor toolbar.
11. Close VBA editor
12. Close the Sample workbook (so you only have your original workbook open now)

To run the macro, press ALT+F8, select CreateExtraction and click on RUN
If the macro runs successfully, you will end on the sheet it produced with the summary.

Originally Posted by RudiS

Hi,

The macro is built according to the structure of the sample file you uploaded. If the sample file is similar in structure to your original file, the macro will run without problems to create the summary. If your original file is structured differently, then the macro might debug with problems...

To transfer the macro into your original file, follow these steps:

1. Open the sample file with the macro in it.
2. Press ALT+F8 to open the Run Macro dialog
3. Select the CreateExtraction macro in the white window and click on Edit.
4. this will take you to the actual macro code.
5. Copy the entire macro fro Sub to End Sub to the clipboard
6. Open the file that contains your Original Data
7. Press ALT+F11 (this opens the VBA editor)
8. In the VBA editor, click the INSERT Menu at the top of the window, and choose MODULE
9. Paste the code you copied into this new blank module
10. Click the SAVE button on the VBA editor toolbar.
11. Close VBA editor
12. Close the Sample workbook (so you only have your original workbook open now)

To run the macro, press ALT+F8, select CreateExtraction and click on RUN
If the macro runs successfully, you will end on the sheet it produced with the summary.

That works perfect,pls guide me what changes i should make if i have to extract even the in & out time or any other data as when needed from the masters

Hi,

I have updated the macro for you.

Copy the code below and paste it over the code in your original workbook, so it replaces the previous macro.

After you have pasted it, you can control what gets printed by adding or deleting the starting comment at the beginning of the lines that start with .offset(1)... in the macro.

Here is the new code to replace the current code you have.

Please Login or Register  to view this content.

Originally Posted by divi123

Please suggest if i wanna add the criteria for extraction

Great to know
I don't understand your question above? What criteria?

You can add multiple criteria below each other like this (for example):

Please Login or Register  to view this content.

However, the filter in your code is using a variable to define which column to apply the filter to as the macro loops through the different date blocks, so just simply putting filter code (as illustrated above) is not going to work in this macro. To define which columns to filter with multiple criteria, it will need to be calculated from the rCell.Column variable by using offsets to go left. For example:

.AutoFilter Field:=rCell.Column, Criteria1:="=E(P)", Operator:=xlOr, Criteria2:="=L(P)"
.AutoFilter Field:=rCell.Offset(0, -3).Column, Criteria1:="G" '(This will add a second filter to your current filter and extract Shift's matching "G")

You can try work it out for yourself to add more criteria based on the example above...

Fine.. cant we make this way?

.AutoFilter Field:=rCell.Column, Criteria1:="=E(P)", Operator:=xlOr, Criteria2:="=L(P)", Operator:=xlOr, Criteria3:="=S(P)"

Pls correct me if i'm wrong... Not to bother you so much but indeed want to learn this rightly

No. Your syntax is not correct. it will work with an array...

.AutoFilter Field:=rCell.Column, Criteria1:=Array("E(P)", "L(P)", "S(P)"), Operator:=xlFilterValues

Okay..

I tried this by just replacing

.AutoFilter Field:=rCell.Column, Criteria1:="=E(P)", Operator:=xlOr, Criteria2:="=L(P)"

with

.AutoFilter Field:=rCell.Column, Criteria1:=Array("E(P)", "L(P)", "S(P)"), Operator:=xlFilterValues

but there was an error message

Autofilter method of range class failed

pls help

It runs fine on the sample file...
Try this:

Please Login or Register  to view this content.

no.. the error still pertains

Is there any difference in the structure of your file compared to the sample file in this post?
There is nothing wrong with the filter definition...it works in the sample file?

You could post the original file if possible. Just remove any confidential/sensitive data.
(Or email directly to me if preferred - ask for my email in a private message)

Hi,

Here is the working macro on your workbook.

The workbook was different to the sample.
First, you had two blank columns separating the names from the time data
Second, you had data below the list of names (I moved this into another sheet called: Fixed Data) Please note that nothing must be below the list because the macro needs to calculate the length of the list in records.
Lastly, the sheet names were sheet1 and sheet2. The macro expects the sheets to be called "Data Sheet" and "Extraction"

These changes prevented the macro from running accurately.

Extract.xls

Hi Rudis,

I tried running macros with the sheet u sent with the name 'Extract"

And it still throws an error. Attached snap shot for ur reference.

Thanks for your prompt revert Rudi... Will be waiting for your workings...

Hi Rudi, think u need to work a little on it. this doesn't extracts the 'Emp.No & Emp.Name' info

sample attached, pls see the extraction sheet