B
bakbuk
Hi,
This problem has bugged me for a week, any help are appreciated.
# Condition
I have 5 sheets:
-Sheet1 : list of text consist of 4 to 5 characters (i.e AAAD,
AAAF,....ABX23) in a single column but many rows.
A B C
===============
1 AXDD
2 AGHO
3 ASDL
4 ...
-Sheet2 : an array contain list of possibilities from AAAA to AZZZ (column
defined as A to Z to the right, row defined as AAA to AZZ to the bottom)
A | B C D E F G H.........AA
==================================
1 | A B C D E F...................
==================================
2 AAA |AAAA AAAB ... ...
3 AAB |...
4 ... |
5 AZZ |AZZA
==================================
-Sheet3 : an array contain list of possibilities from AAA10 to AZZ99 (column
defined as 10 to 99 to the right, row defined as AAA to AZZ to the bottom)
-Sheet4 : similar to Sheet2 but without the list of possibilities, just the
A-Z and AAA-AZZ
-Sheet5 : similar to Sheet3 but without the list of possibilities, just the
10-99 and AAA-AZZ
# What I need
1. Check the value in Sheet1 (per row) whether it's composed of 4 characters
or 5 characters
2. When step1 done, check if the value in Sheet1 whether exist in array
Sheet2 (for 4 chars) or Sheet3 (for 5 chars), and return the cell address
that correspond (will result cell address (if exist) or error (if not exist))
3. When step 2 done, mark the cell (cell address retrieved from step 2) of
Sheet4 (if 4 chars) or Sheet5 (if 5 chars) with "1" if exist, or "0" if not
exist. (write the same cell but different sheet).
# What I've done
1. For checking the text length, I just simply use LEN() function combined
with IF and no problem with this.
2. In Sheet4 and Sheet5, for checking to array, I used
=ADDRESS(SUMPRODUCT(--(Sheet1!$A$1:$A$65536=Sheet2!B2)*ROW(Sheet1!$A$1:$A$65536)),SUMPRODUCT(--(Sheet1!$A$1:$A$65536=Sheet2!B2)*COLUMN(Sheet1!$A$1:$A$65536))
then copy it to the rest of the cell
# Problem
1. For step2, it takes time too much (considering that the excel had to
check the whole possibilities for the list)
2. I got the cell address that matched the array....well then, how can I
"order" the excel to write "1" or "0" at the Sheet4 or Sheet5 in the cell
whose address is retrieved before...
3. If I add a new entry at Sheet1, Problem 1 happened again....so it proved
me this will take too much time every time a new data added.
# now.....any help? (@_@) many thanks before
This problem has bugged me for a week, any help are appreciated.
# Condition
I have 5 sheets:
-Sheet1 : list of text consist of 4 to 5 characters (i.e AAAD,
AAAF,....ABX23) in a single column but many rows.
A B C
===============
1 AXDD
2 AGHO
3 ASDL
4 ...
-Sheet2 : an array contain list of possibilities from AAAA to AZZZ (column
defined as A to Z to the right, row defined as AAA to AZZ to the bottom)
A | B C D E F G H.........AA
==================================
1 | A B C D E F...................
==================================
2 AAA |AAAA AAAB ... ...
3 AAB |...
4 ... |
5 AZZ |AZZA
==================================
-Sheet3 : an array contain list of possibilities from AAA10 to AZZ99 (column
defined as 10 to 99 to the right, row defined as AAA to AZZ to the bottom)
-Sheet4 : similar to Sheet2 but without the list of possibilities, just the
A-Z and AAA-AZZ
-Sheet5 : similar to Sheet3 but without the list of possibilities, just the
10-99 and AAA-AZZ
# What I need
1. Check the value in Sheet1 (per row) whether it's composed of 4 characters
or 5 characters
2. When step1 done, check if the value in Sheet1 whether exist in array
Sheet2 (for 4 chars) or Sheet3 (for 5 chars), and return the cell address
that correspond (will result cell address (if exist) or error (if not exist))
3. When step 2 done, mark the cell (cell address retrieved from step 2) of
Sheet4 (if 4 chars) or Sheet5 (if 5 chars) with "1" if exist, or "0" if not
exist. (write the same cell but different sheet).
# What I've done
1. For checking the text length, I just simply use LEN() function combined
with IF and no problem with this.
2. In Sheet4 and Sheet5, for checking to array, I used
=ADDRESS(SUMPRODUCT(--(Sheet1!$A$1:$A$65536=Sheet2!B2)*ROW(Sheet1!$A$1:$A$65536)),SUMPRODUCT(--(Sheet1!$A$1:$A$65536=Sheet2!B2)*COLUMN(Sheet1!$A$1:$A$65536))
then copy it to the rest of the cell
# Problem
1. For step2, it takes time too much (considering that the excel had to
check the whole possibilities for the list)
2. I got the cell address that matched the array....well then, how can I
"order" the excel to write "1" or "0" at the Sheet4 or Sheet5 in the cell
whose address is retrieved before...
3. If I add a new entry at Sheet1, Problem 1 happened again....so it proved
me this will take too much time every time a new data added.
# now.....any help? (@_@) many thanks before