You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
integer filter
- Thread starter leader2k
- Start date
NARAYANK991
Excel Ninja
Hi ,
Can you give more details , or even better , upload a sample workbook ?
Narayan
Can you give more details , or even better , upload a sample workbook ?
Narayan
HiHi ,
Can you give more details , or even better , upload a sample workbook ?
Narayan
i have a worksheet with database No:1 A1 to F100000 (A1,B1,C1,D1,E1,F1 TO A100000,B100000,C100000,D100000,E100000,F100000) and i have database No:2(H1,I1,J1,K1,L1,M1TO H3000,I3000,J3000,K3000,L3000,M3000)so i want to loop through database1 against database2 and show rows that match 4,5 or 6 value only , i appreciate the reply. My regards.
NARAYANK991
Excel Ninja
Hi ,
Let me understand this my way :
1. You have 2 ranges of data , A1 : F100000 and H1 : M3000.
2. You wish to go through the first range , and which row has data that matches any row in the second range , in at least 4 cells , then highlight that row.
Even though I had requested a sample workbook upload , since that has not been done , more time will be wasted trying to get clarifications. Please clarify the following :
a. Do the cells have to be in the same column order or can the matches be in any order ? For example , suppose a row in range 1 has the following data :
3 , 13 , 22 , 11 , 17 , 45
Suppose a row in range 2 has the following data :
3 , 11 , 22 , 56 , 45 , 22
Will the first row match the second ?
Will the duplicates in the second row count as 1 match or 2 matches ?
b. Can more than 1 row from range 1 match the same row in range 2 ?
c. Can 1 row in range 1 match more than 1 row in range 2 ?
d. Do you want a formula-based solution or do you want a VBA solution ?
Narayan
Let me understand this my way :
1. You have 2 ranges of data , A1 : F100000 and H1 : M3000.
2. You wish to go through the first range , and which row has data that matches any row in the second range , in at least 4 cells , then highlight that row.
Even though I had requested a sample workbook upload , since that has not been done , more time will be wasted trying to get clarifications. Please clarify the following :
a. Do the cells have to be in the same column order or can the matches be in any order ? For example , suppose a row in range 1 has the following data :
3 , 13 , 22 , 11 , 17 , 45
Suppose a row in range 2 has the following data :
3 , 11 , 22 , 56 , 45 , 22
Will the first row match the second ?
Will the duplicates in the second row count as 1 match or 2 matches ?
b. Can more than 1 row from range 1 match the same row in range 2 ?
c. Can 1 row in range 1 match more than 1 row in range 2 ?
d. Do you want a formula-based solution or do you want a VBA solution ?
Narayan
HiHi ,
Let me understand this my way :
1. You have 2 ranges of data , A1 : F100000 and H1 : M3000.
2. You wish to go through the first range , and which row has data that matches any row in the second range , in at least 4 cells , then highlight that row.
Even though I had requested a sample workbook upload , since that has not been done , more time will be wasted trying to get clarifications. Please clarify the following :
a. Do the cells have to be in the same column order or can the matches be in any order ? For example , suppose a row in range 1 has the following data :
3 , 13 , 22 , 11 , 17 , 45
Suppose a row in range 2 has the following data :
3 , 11 , 22 , 56 , 45 , 22
Will the first row match the second ?
Will the duplicates in the second row count as 1 match or 2 matches ?
b. Can more than 1 row from range 1 match the same row in range 2 ?
c. Can 1 row in range 1 match more than 1 row in range 2 ?
d. Do you want a formula-based solution or do you want a VBA solution ?
Narayan
Order doesn't matter ,each row from database No2 compaired against database No1 and highlight rows that contain at least 4 matches out of 6.
very appreciatif of the help.
My regards
NARAYANK991
Excel Ninja
Hi ,
I am uploading a file where I have put in 2 tables named Table1 and Table2.
Both have 6 columns each.
The file has a macro named Compare_Table2_with_Table1 which when run , puts in some values in columns N and O in each row that has at least 4 matches in a row of Table1.
The value in column N is 1 to signify that a match has been found ; the value in column O is the corresponding row number in Table1 which has the matches.
If you wish to transfer the code in this file to yours , you need to do the following :
1. Ensure that your data is in tables ; if not convert the data ranges to tables
2. If your tables are not named Table1 and Table2 , either rename them to these names , or change the references within the code to your table names.
3. Copy the code and paste it in the sheet section corresponding to the sheet which has the tables.
If you can run the code , and give some feedback on how much time it takes , we can take it from there.
Narayan
I am uploading a file where I have put in 2 tables named Table1 and Table2.
Both have 6 columns each.
The file has a macro named Compare_Table2_with_Table1 which when run , puts in some values in columns N and O in each row that has at least 4 matches in a row of Table1.
The value in column N is 1 to signify that a match has been found ; the value in column O is the corresponding row number in Table1 which has the matches.
If you wish to transfer the code in this file to yours , you need to do the following :
1. Ensure that your data is in tables ; if not convert the data ranges to tables
2. If your tables are not named Table1 and Table2 , either rename them to these names , or change the references within the code to your table names.
3. Copy the code and paste it in the sheet section corresponding to the sheet which has the tables.
If you can run the code , and give some feedback on how much time it takes , we can take it from there.
Narayan
Attachments
Hi ,
I am uploading a file where I have put in 2 tables named Table1 and Table2.
Both have 6 columns each.
The file has a macro named Compare_Table2_with_Table1 which when run , puts in some values in columns N and O in each row that has at least 4 matches in a row of Table1.
The value in column N is 1 to signify that a match has been found ; the value in column O is the corresponding row number in Table1 which has the matches.
If you wish to transfer the code in this file to yours , you need to do the following :
1. Ensure that your data is in tables ; if not convert the data ranges to tables
2. If your tables are not named Table1 and Table2 , either rename them to these names , or change the references within the code to your table names.
3. Copy the code and paste it in the sheet section corresponding to the sheet which has the tables.
If you can run the code , and give some feedback on how much time it takes , we can take it from there.
Narayan
First of all I thank you very kindley for the help,it’s appreciated,but the solution it’s coming short of what I want ,which consist of running each row from table 2 against each row from table 1 and display the the rows that containing 4 matching numbers and up. I can use the function //=sumproduct(countif($H$1:$M$1,A1:F1)) and I filter manually the matching 4-5-6 ,but it’s time consuming and I have to compare each row from table 2 to table 1 (one by one) instead of having a vba macro that loop through Matching (H1:M1 to each row of Table 1 & go to H2:M2 match & H3:M3 do the same…….
My regards
NARAYANK991
Excel Ninja
Hi ,
It is not the solution which is coming short , but communication.
I am sorry , but I hope someone else will step in and help out.
Narayan
It is not the solution which is coming short , but communication.
I am sorry , but I hope someone else will step in and help out.
Narayan
Hi ,
It is not the solution which is coming short , but communication.
I am sorry , but I hope someone else will step in and help out.
Narayan
Thanx anyway
My regards