Jeet
New Member
Hello Everyone..!
I need a solution for the 3 problems which I have mentioned here with detailed Step by Step procedure of the whole process & conditions to be fulfilled.
I would like to find a formula that searches for any three digits match from the given Input set cells in database columns, and then highlights the cells in column so it can be cut and inserted in a new location.
Kindly check it & help me in solving this.
Problem No.1:
Total Database Size is “ $A$1:$I$1000 ” (9 columns & 1000 rows)
The Input cells with search values are located as follows:
A1001= 5721 Input 1
A1002= 2446 Input 2
Step 1:
Take A1001’s value (5721) and check if any cell from $A$1:$I$1000 having any of its 3 digits matched.
If yes then Proceed with Step 2.
If no match found then stop searching
Step 2:
If any 3 digits match found for the Input 1, A1001’s value then,
Check the value of the cell below the match found cell
Example: if the Input 1’s 3 digits match was found in a cell A24 then check the value of the cell below it, which is A25.
Check if it’s having any 3 digits from the Input 2, A1002’s value (2446)
If yes then highlight the two cells (i.e.) A24 & A25.
If no match found then stop searching.
Note:
Let’s take Input A1001= 5721 value as example.
Now what I mean by search any 3 digits match is
The formula has to find the cell (from$A$1:$I$1000) which have any 3 digits from A1001 (5721)
i.e.) any cell (from$A$1:$I$1000) having the following set of digits in it
(5, 7, 2) (5, 7, 1) (7, 2, 1) (5, 2, 1)
==========================================================
Problem No.2:
Total Database Size is “ $A$1:$I$1000 ” (9 columns & 1000 rows)
The Input cells with search values are located as follows:
A1001= 5721; B1001= 6067; C1001= 8901 <== Input Set 1
A1002= 2446; B1002= 3549; C1002= 5401 <== Input Set 2
In this Problem 2 more Input values are added in each Input set.
The Output order should not be changed:
Top cell value must have any 3 digit match from Input set 1 (A1001/B1001/C1001)
Bottom cell value must have any 3 digit match from Input set 2 (A1002/B1002/C1002)
Step 1:
Take A1001’s value (5721) and check if any cell from $A$1:$I$1000 having any of its 3 digits matched.
If yes then Proceed with Step 2.
If No match found then take B1001’s value (6067) and check if any cell from $A$1:$I$1000 having any of its 3 digits matched.
If yes then Proceed with Step 2.
If No match found then take C1001’s value (8901) and check if any cell from $A$1:$I$1000 having any of its 3 digits matched.
If yes then Proceed with Step 2.
If no match found for all three Input set 1 (A1001, B1001, C1001) then stop searching
Step 2:
If any 3 digits match found for the Input set 1’s (A1001 or B1001 or C1001) value then,
Check the value of the cell below the match found cell
Example: if the Input set 1’s 3 digits match was found in a cell A24 then check the value of the cell below it, which is A25.
Check if it’s having any 3 digits from the Input set 2’s A1002’s value (2446)
If yes then highlight the two cells (i.e.) A24 & A25.
If No match found then take B1002’s value (3549) and check if it’s having any of its 3 digits matched.
If yes then highlight the two cells (i.e.) A24 & A25.
If No match found then take C1002’s value (5401) and check if it’s having any of its 3 digits matched.
If yes then highlight all the two cells (i.e.) A24 & A25.
If no match found for Input set 2 (A1002, B1002, C1002) then stop searching
In short the cells A24 & A25 are highlighted because:
A24 value – is having any 3 digits matched from Input set 1’s value
A25 value – is having any 3 digits matched from Input set 2’s value
==========================================================
Problem No.3:
Total Database Size is “ $A$1:$I$1000 ” (9 columns & 1000 rows)
The Input cells with search values are located as follows:
A1001= 5721; B1001= 6067; C1001= 8901 <== Input Set 1
A1002= 2446; B1002= 3549; C1002= 5401 <== Input Set 2
A1003= 8321; B1003= 3250; C1003= 9936 <== Input Set 3
In this Problem 1 more Input Set is added.
The Output order should not be changed:
Top cell value must have any 3 digit match from Input set 1 (A1001/B1001/C1001)
Middle cell value must have any 3 digit match from Input set 2 (A1002/B1002/C1002)
Bottom cell value must have any 3 digit match from Input set 3 (A1003/B1003/C1003)
Step 1:
Take A1001’s value (5721) and check if any cell from $A$1:$I$1000 having any of its 3 digits matched.
If yes then Proceed with Step 2.
If No match found then take B1001’s value (6067) and check if any cell from $A$1:$I$1000 having any of its 3 digits matched.
If yes then Proceed with Step 2.
If No match found then take C1001’s value (8901) and check if any cell from $A$1:$I$1000 having any of its 3 digits matched.
If yes then Proceed with Step 2.
If no match found for all three Input set 1 (A1001, B1001, C1001) then stop searching
Step 2:
If any 3 digits match found for the Input set 1’s (A1001 or B1001 or C1001) value then,
Check the value of the cell below the match found cell
Example: if the Input set 1’s 3 digits match was found in a cell A24 then check the value of the cell below it, which is A25.
Check if it’s having any 3 digits from the Input set 2’s A1002’s value (2446)
If yes then Proceed with Step 3.
If No match found then take B1002’s value (3549) and check if it’s having any of its 3 digits matched.
If yes then Proceed with Step 3.
If No match found then take C1002’s value (5401) and check if it’s having any of its 3 digits matched.
If yes then Proceed with Step 3.
If no match found for all three Input set 2 (A1002, B1002, C1002) then stop searching
Step 3:
If the Input set 1 and Input set 2’s match found
Example:
Let’s Assume that any one of the 3 digits from 1st Input (A1001, B1001, C1001) matched in cell A24 and any one of the 3 digits from 2nd Input (A1002, B1002, C1002) matched in A25 then check the value of the cell below it, which is A26.
Check if it’s having any 3 digits from the Input set 3 A1003’s value (8321)
If yes then highlight all the three cells (i.e.) A24 & A25 & A26.
If No match found then take B1003’s value (3250) and check if it’s having any of its 3 digits matched.
If yes then highlight all the three cells (i.e.) A24 & A25 & A26.
If No match found then take C1003’s value (9936) and check if it’s having any of its 3 digits matched.
If yes then highlight all the three cells (i.e.) A24 & A25 & A26.
If no match found for all three Input set 3 (A1003, B1003, C1003) then stop searching
In short the three cells A24 & A25 & A26 are highlighted because:
A24 value – is having any 3 digits matched from Input set 1’s value
A25 value – is having any 3 digits matched from Input set 2’s value
A26 value – is having any 3 digits matched from Input set 3’s value
=========================================================
Many Thanks in Advance & Regards,
Jeet
I need a solution for the 3 problems which I have mentioned here with detailed Step by Step procedure of the whole process & conditions to be fulfilled.
I would like to find a formula that searches for any three digits match from the given Input set cells in database columns, and then highlights the cells in column so it can be cut and inserted in a new location.
Kindly check it & help me in solving this.
Problem No.1:
Total Database Size is “ $A$1:$I$1000 ” (9 columns & 1000 rows)
The Input cells with search values are located as follows:
A1001= 5721 Input 1
A1002= 2446 Input 2
Step 1:
Take A1001’s value (5721) and check if any cell from $A$1:$I$1000 having any of its 3 digits matched.
If yes then Proceed with Step 2.
If no match found then stop searching
Step 2:
If any 3 digits match found for the Input 1, A1001’s value then,
Check the value of the cell below the match found cell
Example: if the Input 1’s 3 digits match was found in a cell A24 then check the value of the cell below it, which is A25.
Check if it’s having any 3 digits from the Input 2, A1002’s value (2446)
If yes then highlight the two cells (i.e.) A24 & A25.
If no match found then stop searching.
Note:
Let’s take Input A1001= 5721 value as example.
Now what I mean by search any 3 digits match is
The formula has to find the cell (from$A$1:$I$1000) which have any 3 digits from A1001 (5721)
i.e.) any cell (from$A$1:$I$1000) having the following set of digits in it
(5, 7, 2) (5, 7, 1) (7, 2, 1) (5, 2, 1)
==========================================================
Problem No.2:
Total Database Size is “ $A$1:$I$1000 ” (9 columns & 1000 rows)
The Input cells with search values are located as follows:
A1001= 5721; B1001= 6067; C1001= 8901 <== Input Set 1
A1002= 2446; B1002= 3549; C1002= 5401 <== Input Set 2
In this Problem 2 more Input values are added in each Input set.
The Output order should not be changed:
Top cell value must have any 3 digit match from Input set 1 (A1001/B1001/C1001)
Bottom cell value must have any 3 digit match from Input set 2 (A1002/B1002/C1002)
Step 1:
Take A1001’s value (5721) and check if any cell from $A$1:$I$1000 having any of its 3 digits matched.
If yes then Proceed with Step 2.
If No match found then take B1001’s value (6067) and check if any cell from $A$1:$I$1000 having any of its 3 digits matched.
If yes then Proceed with Step 2.
If No match found then take C1001’s value (8901) and check if any cell from $A$1:$I$1000 having any of its 3 digits matched.
If yes then Proceed with Step 2.
If no match found for all three Input set 1 (A1001, B1001, C1001) then stop searching
Step 2:
If any 3 digits match found for the Input set 1’s (A1001 or B1001 or C1001) value then,
Check the value of the cell below the match found cell
Example: if the Input set 1’s 3 digits match was found in a cell A24 then check the value of the cell below it, which is A25.
Check if it’s having any 3 digits from the Input set 2’s A1002’s value (2446)
If yes then highlight the two cells (i.e.) A24 & A25.
If No match found then take B1002’s value (3549) and check if it’s having any of its 3 digits matched.
If yes then highlight the two cells (i.e.) A24 & A25.
If No match found then take C1002’s value (5401) and check if it’s having any of its 3 digits matched.
If yes then highlight all the two cells (i.e.) A24 & A25.
If no match found for Input set 2 (A1002, B1002, C1002) then stop searching
In short the cells A24 & A25 are highlighted because:
A24 value – is having any 3 digits matched from Input set 1’s value
A25 value – is having any 3 digits matched from Input set 2’s value
==========================================================
Problem No.3:
Total Database Size is “ $A$1:$I$1000 ” (9 columns & 1000 rows)
The Input cells with search values are located as follows:
A1001= 5721; B1001= 6067; C1001= 8901 <== Input Set 1
A1002= 2446; B1002= 3549; C1002= 5401 <== Input Set 2
A1003= 8321; B1003= 3250; C1003= 9936 <== Input Set 3
In this Problem 1 more Input Set is added.
The Output order should not be changed:
Top cell value must have any 3 digit match from Input set 1 (A1001/B1001/C1001)
Middle cell value must have any 3 digit match from Input set 2 (A1002/B1002/C1002)
Bottom cell value must have any 3 digit match from Input set 3 (A1003/B1003/C1003)
Step 1:
Take A1001’s value (5721) and check if any cell from $A$1:$I$1000 having any of its 3 digits matched.
If yes then Proceed with Step 2.
If No match found then take B1001’s value (6067) and check if any cell from $A$1:$I$1000 having any of its 3 digits matched.
If yes then Proceed with Step 2.
If No match found then take C1001’s value (8901) and check if any cell from $A$1:$I$1000 having any of its 3 digits matched.
If yes then Proceed with Step 2.
If no match found for all three Input set 1 (A1001, B1001, C1001) then stop searching
Step 2:
If any 3 digits match found for the Input set 1’s (A1001 or B1001 or C1001) value then,
Check the value of the cell below the match found cell
Example: if the Input set 1’s 3 digits match was found in a cell A24 then check the value of the cell below it, which is A25.
Check if it’s having any 3 digits from the Input set 2’s A1002’s value (2446)
If yes then Proceed with Step 3.
If No match found then take B1002’s value (3549) and check if it’s having any of its 3 digits matched.
If yes then Proceed with Step 3.
If No match found then take C1002’s value (5401) and check if it’s having any of its 3 digits matched.
If yes then Proceed with Step 3.
If no match found for all three Input set 2 (A1002, B1002, C1002) then stop searching
Step 3:
If the Input set 1 and Input set 2’s match found
Example:
Let’s Assume that any one of the 3 digits from 1st Input (A1001, B1001, C1001) matched in cell A24 and any one of the 3 digits from 2nd Input (A1002, B1002, C1002) matched in A25 then check the value of the cell below it, which is A26.
Check if it’s having any 3 digits from the Input set 3 A1003’s value (8321)
If yes then highlight all the three cells (i.e.) A24 & A25 & A26.
If No match found then take B1003’s value (3250) and check if it’s having any of its 3 digits matched.
If yes then highlight all the three cells (i.e.) A24 & A25 & A26.
If No match found then take C1003’s value (9936) and check if it’s having any of its 3 digits matched.
If yes then highlight all the three cells (i.e.) A24 & A25 & A26.
If no match found for all three Input set 3 (A1003, B1003, C1003) then stop searching
In short the three cells A24 & A25 & A26 are highlighted because:
A24 value – is having any 3 digits matched from Input set 1’s value
A25 value – is having any 3 digits matched from Input set 2’s value
A26 value – is having any 3 digits matched from Input set 3’s value
=========================================================
Many Thanks in Advance & Regards,
Jeet
