Transfer data from one worksheet to another based on criteria

[ash2017]

Hi
I wanted to transfer data from TEST 1 to TEST 2 workbook based on the criteria in TEST 2 workbook. Sample workbooks uploaded.
I am not sure if this is possible. At least the data transfer in specific date range (criteria- Date) will also be helpful.

Many thanks for your kind help!

[Sintek]

Hi ash2017

Help us understand what you are trying to achieve...
Are you wanting to enter information into the blocks and then pressa button for it to be entered into the rows under appropriate headers on other sheet?

[ash2017]

Hi Sintek,
Yes, you are right.
Many thanks

[CAntosh]

Try the following formula in A16 of Test2. It should be array-entered (confirm with Ctrl + Shift + Enter instead of Enter):

=IFERROR(INDEX('[TEST 1.xlsx]Tracker'!$A$3:$A$11,SMALL(IF((IF($B$1="",1,$B$1='[TEST 1.xlsx]Tracker'!$B$3:$B$11))*(IF($B$3="",1,$B$3='[TEST 1.xlsx]Tracker'!$C$3:$C$11))*(IF($B$5="",1,$B$5='[TEST 1.xlsx]Tracker'!$F$3:$F$11))*(IF($B$7="",1,$B$7<='[TEST 1.xlsx]Tracker'!$G$3:$G$11))*(IF($D$7="",1,$D$7>='[TEST 1.xlsx]Tracker'!$G$3:$G$11))*(IF($B$9="",1,$B$9='[TEST 1.xlsx]Tracker'!$H$3:$H$11))*(IF($B$11="",1,$B$11<='[TEST 1.xlsx]Tracker'!$I$3:$I$11))*(IF($D$11="",1,$D$11>='[TEST 1.xlsx]Tracker'!$I$3:$I$11))*(IF($B$13="",1,$B$13='[TEST 1.xlsx]Tracker'!$J$3:$J$11))=1,ROW('[TEST 1.xlsx]Tracker'!$A$3:$A$11)),ROW(1:1))-2),"")

This should return the SI number of entries that match the criteria. You can then use a regular look up to get the rest of the data. Enter the following (non-array) in B16:

=IFERROR(INDEX('[TEST 1.xlsx]Tracker'!B$3:B$11,MATCH($A16,'[TEST 1.xlsx]Tracker'!$A$3:$A$11,0)),"")

Fill right through J16, then fill everything down. Make sure the file path matches. It would help if you have them both open when you first enter the equation. It appears to be working for me, but give it a try to see if it will do.

[ash2017]

Hi CAntosh,
Thank you. Actually I am looking for VBA code

[Sintek]

Hi Sintek,
Yes, you are right.
Many thanks

Is this a client query log list that you will be working on....So will need to be edited daily etc.

[ash2017]

Is this a client query log list that you will be working on....So will need to be edited daily etc.

Yes the data will be included in TEST 1 everyday. TEST 2 will only populate rows from TEST 1 based on criteria.

[Sintek]

K, what criteria....Edited.

k so Date criteria I see....So what, if certain date do what?

[ash2017]

K, what criteria

Client ref no.

Issue type Technical

Initial/Follow-up Initial

Date 3-Apr-17 to 4-Apr-17

Resolved (Y/N) Yes

Date of resolution to

On time (Y/N) Yes

[Sintek]

ash2017
Sorry, still not getting required result you want.....As per your sample workbooks.
Test 1: You fill in the boxes Then you press a button and Test2 gets filled.
Then you type in new info in blocks and Test2 gets filled from nextrow.
So when you need to edit and update, you then go to Test2 and make changes?

[ash2017]

Hi Sintek,
My apology.
TEST1 workbook is a client query log list and will be updated everyday. This is a simple excel sheet.

TEST 2 workbook,
When information (filter criteria) included in the blocks (Client ref no.,Issue type,Initial/Follow-up,Date,Resolved (Y/N),Date of resolution,On time (Y/N)) and button pressed, the data will be populated below row 16. The data will be populated from TEST 1.

[Sintek]

Perhaps you should upload another sample....Use only one workbook with two sheets named Test1 & Test 2
Then you manually enter information and highlight the result you want in Test2. Give a few examples.

[ash2017]

now I have included in two worksheets. In report tab I want the information to be generated. I also limited the criteria (only date range).

[CK76]

@ash2017
One major issue. Non of your data match your criteria.

Edit: This is referring to your original 2 workbooks.

[ash2017]

@ash2017
One major issue. Non of your data match your criteria.

Edit: This is referring to your original 2 workbooks.

Hi CK76,
Please Check the second upload. In that filter criteria is date. Column G

[Sintek]

This should do the trick

Please Login or Register  to view this content.

[ash2017]

@ash2017
One major issue. Non of your data match your criteria.

Edit: This is referring to your original 2 workbooks.

Actually in that I was thinking if some filter criteria are blank, the data will be generated based on the criteria mentioned.

[Sintek]

One then has to put a search parameter in for each criteria and not just date.

[ash2017]

One then has to put a search parameter in for each criteria and not just date.

Your are Genius!
Can we populate data only between two date range. Something like Criteria1:=">=” & Range("B2"), Criteria2:="<=” & Range("D2"). Also what if two different workbook. This is because 1 sheet will be used by employees. the other sheet will be kept separately.

[Sintek]

so in other words if B2 = 01/04/2017 and D2 = 02/04/2017 Then info for both dates must be populated.

[ash2017]

yes if we include more dates, then only the data between two date range will be populated.
Also Can we overwrite the previous data. Currently every time I press the button the same data populate in next rows.

[Sintek]

yes if we include more dates, then only the data between two date range will be populated.
Also Can we overwrite the previous data. Currently every time I press the button the same data populate in next rows.

Edited and amended

Please Login or Register  to view this content.

b

[ash2017]

Try this for current sample workbook.

Please Login or Register  to view this content.

Perfect! Can we avoid the duplication. Now every time we press the same data populate in rows

[CK76]

Here's another method. Just using Date column as condition. Put both workbooks in same folder.

Click on "Get Data" button in Report sheet of Test 2.xlsm

Please Login or Register  to view this content.

EDIT: Forgot to add rst.Close & cn.Close in the sample workbook. Add those lines at the end of code.

[Sintek]

Perfect! Can we avoid the duplication. Now every time we press the same data populate in rows

See Post #22

[ash2017]

Thanks CK76

[ash2017]

Thank You Sintek