S
Sumit
Here is a problem I'm facing currently while trying to prepare a wiring and
termination chart in excel where I need to automatically deduce cable IDs
from connector IDs using concatenate function.
Data from two columns ID1 and ID2 are to be concatenated in column CONC but
with an exception. Let's take row 7 with A=8, B=4 and C=Cell C7. The formula
should search in the range C2:C6 and if it finds the result of the function
"=concatenate(B7,A7)", it should return the same (which should be 48 in this
case), otherwise should return the result of "=concatenate(A7,B7) in cell
C7,i.e. 84. In Col D I have listed the values that I desire to be returned by
the formula in col C.
ID1 ID2 CONC DESIRED VALUES
1 9 #N/A 19
2 8 #N/A 28
3 7 #N/A 37
4 6 #N/A 46
5 5 #N/A 55
8 4 #N/A 84
7 3 #N/A 37
8 2 #N/A 28
9 1 #N/A 19
The formula I used is:
=IF(INDEX($C$1:C6,MATCH(CONCATENATE(B7,A7),($C$1:C6),0)),CONCATENATE(B7,A7),CONCATENATE(A7,B7))
I keep getting an error #N/A though at one time I did get a correct answer.
Don't know what happened? Any help please?
termination chart in excel where I need to automatically deduce cable IDs
from connector IDs using concatenate function.
Data from two columns ID1 and ID2 are to be concatenated in column CONC but
with an exception. Let's take row 7 with A=8, B=4 and C=Cell C7. The formula
should search in the range C2:C6 and if it finds the result of the function
"=concatenate(B7,A7)", it should return the same (which should be 48 in this
case), otherwise should return the result of "=concatenate(A7,B7) in cell
C7,i.e. 84. In Col D I have listed the values that I desire to be returned by
the formula in col C.
ID1 ID2 CONC DESIRED VALUES
1 9 #N/A 19
2 8 #N/A 28
3 7 #N/A 37
4 6 #N/A 46
5 5 #N/A 55
8 4 #N/A 84
7 3 #N/A 37
8 2 #N/A 28
9 1 #N/A 19
The formula I used is:
=IF(INDEX($C$1:C6,MATCH(CONCATENATE(B7,A7),($C$1:C6),0)),CONCATENATE(B7,A7),CONCATENATE(A7,B7))
I keep getting an error #N/A though at one time I did get a correct answer.
Don't know what happened? Any help please?