Excel Spreadsheet
 

I have four columns (A, b, c, d). What could I do to have each cell in "A"
check all cells in "B" to see if their is a match. If there is a match in
"C" take the value in "D" and place it in "B" next to the match in column "A".
Unit(A) #of Admissions(B) Unit(C) #of
admissions(D)
CCU 5 TICU
2
ICU CCU
5
TICU ICU
4

Goal: I want to populate "B" with the information from "D" next to the
correct unit.

Thank you,
Kem

Excel Spreadsheet
 

I think you want this.
=SUMIF($C$2:$C$100,A2,$D$2:$D$100)
copy down for each unit listed in column A, expand the range beyond 100, or
shorten it up as needed.
Was confused by your comment: ...have each cell in "A" check all cells in
"B" to see...
I think you are checking column C, as you state in the next sentence.
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"khicks4u" wrote:

I have four columns (A, b, c, d). What could I do to have each cell in "A"
check all cells in "B" to see if their is a match. If there is a match in
"C" take the value in "D" and place it in "B" next to the match in column "A".
Unit(A) #of Admissions(B) Unit(C) #of
admissions(D)
CCU 5 TICU
2
ICU CCU
5
TICU ICU
4

Goal: I want to populate "B" with the information from "D" next to the
correct unit.

Thank you,
Kem

Excel Spreadsheet
 

Thank you John for your help. I do not think I explained myself correctly.
What I want to do is see if there is a match for the cells in column "A" in
Column"C". If there is a match I want to take the value in column "D" for
the match in column "C" and enter it in column "B" next to the match in
column "A". For example there is not a match in column "C" for CVIC so the
cell in Column "B" would stay empty. There is a match for CCU1 in column "C"
so the value of "1" would then be entered in the cooresponding cell in column
"B"
CCU1 1 CCU1 1
CTUN 1 CTUN 1
CTUS CTUS 3
CVIC CVSD 1
CVSD 1 NSIC 2
NSIC 2 NULL 5
NULL 5 O4 1
O4 1 PCCU 2
O5E T10E 2
PCCU 2 T10W 1
T10E 2 T2 3
T10W 1 T3IC 2
T2 3 T6W 1
T3E T7E 1
T3IC 2 T7W 1
T4AD TICU 1
T6W 1 W5E 1
T7E 1 W5N 2
T7W 1
T8E
T8W
T9W
TICU 1
W5E 1
W5N 2
W7N


"John C" wrote:

I think you want this.
=SUMIF($C$2:$C$100,A2,$D$2:$D$100)
copy down for each unit listed in column A, expand the range beyond 100, or
shorten it up as needed.
Was confused by your comment: ...have each cell in "A" check all cells in
"B" to see...
I think you are checking column C, as you state in the next sentence.
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"khicks4u" wrote:

I have four columns (A, b, c, d). What could I do to have each cell in "A"
check all cells in "B" to see if their is a match. If there is a match in
"C" take the value in "D" and place it in "B" next to the match in column "A".
Unit(A) #of Admissions(B) Unit(C) #of
admissions(D)
CCU 5 TICU
2
ICU CCU
5
TICU ICU
4

Goal: I want to populate "B" with the information from "D" next to the
correct unit.

Thank you,
Kem

Excel Spreadsheet
 

Okay, didn't know about the blanks. If there are only single occurances of
each clinic in column C (i.e.: only 1 T2, only 1 T3IC, etc), then you should
be able to use this formula:
=IF(ISNA(VLOOKUP(A2,$C$2:$D$100,2,FALSE)),"",VLOOK UP(A2,$C$2:$D$100,2,FALSE))

If, however, there may be multiple occurances of the same clinic in column
C, then you need to sum them up, (i.e.: T2=4, and T2=6, so you would want the
T2=10 for column A:B, you can use my original formula, but slightly modified:
=IF(SUMIF($C$2:$C$100,A2,$D$2:$D$100)=0,"",SUMIF($ C$2:$C$100,A2,$D$2:$D$100))
--
** John C **



"khicks4u" wrote:

Thank you John for your help. I do not think I explained myself correctly.
What I want to do is see if there is a match for the cells in column "A" in
Column"C". If there is a match I want to take the value in column "D" for
the match in column "C" and enter it in column "B" next to the match in
column "A". For example there is not a match in column "C" for CVIC so the
cell in Column "B" would stay empty. There is a match for CCU1 in column "C"
so the value of "1" would then be entered in the cooresponding cell in column
"B"
CCU1 1 CCU1 1
CTUN 1 CTUN 1
CTUS CTUS 3
CVIC CVSD 1
CVSD 1 NSIC 2
NSIC 2 NULL 5
NULL 5 O4 1
O4 1 PCCU 2
O5E T10E 2
PCCU 2 T10W 1
T10E 2 T2 3
T10W 1 T3IC 2
T2 3 T6W 1
T3E T7E 1
T3IC 2 T7W 1
T4AD TICU 1
T6W 1 W5E 1
T7E 1 W5N 2
T7W 1
T8E
T8W
T9W
TICU 1
W5E 1
W5N 2
W7N


"John C" wrote:

I think you want this.
=SUMIF($C$2:$C$100,A2,$D$2:$D$100)
copy down for each unit listed in column A, expand the range beyond 100, or
shorten it up as needed.
Was confused by your comment: ...have each cell in "A" check all cells in
"B" to see...
I think you are checking column C, as you state in the next sentence.
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"khicks4u" wrote:

I have four columns (A, b, c, d). What could I do to have each cell in "A"
check all cells in "B" to see if their is a match. If there is a match in
"C" take the value in "D" and place it in "B" next to the match in column "A".
Unit(A) #of Admissions(B) Unit(C) #of
admissions(D)
CCU 5 TICU
2
ICU CCU
5
TICU ICU
4

Goal: I want to populate "B" with the information from "D" next to the
correct unit.

Thank you,
Kem

Excel Spreadsheet
 

I will definately try this. Thank you so much for taking the time to help us
out.
Sincerely
Kem

"John C" wrote:

Okay, didn't know about the blanks. If there are only single occurances of
each clinic in column C (i.e.: only 1 T2, only 1 T3IC, etc), then you should
be able to use this formula:
=IF(ISNA(VLOOKUP(A2,$C$2:$D$100,2,FALSE)),"",VLOOK UP(A2,$C$2:$D$100,2,FALSE))

If, however, there may be multiple occurances of the same clinic in column
C, then you need to sum them up, (i.e.: T2=4, and T2=6, so you would want the
T2=10 for column A:B, you can use my original formula, but slightly modified:
=IF(SUMIF($C$2:$C$100,A2,$D$2:$D$100)=0,"",SUMIF($ C$2:$C$100,A2,$D$2:$D$100))
--
** John C **



"khicks4u" wrote:

Thank you John for your help. I do not think I explained myself correctly.
What I want to do is see if there is a match for the cells in column "A" in
Column"C". If there is a match I want to take the value in column "D" for
the match in column "C" and enter it in column "B" next to the match in
column "A". For example there is not a match in column "C" for CVIC so the
cell in Column "B" would stay empty. There is a match for CCU1 in column "C"
so the value of "1" would then be entered in the cooresponding cell in column
"B"
CCU1 1 CCU1 1
CTUN 1 CTUN 1
CTUS CTUS 3
CVIC CVSD 1
CVSD 1 NSIC 2
NSIC 2 NULL 5
NULL 5 O4 1
O4 1 PCCU 2
O5E T10E 2
PCCU 2 T10W 1
T10E 2 T2 3
T10W 1 T3IC 2
T2 3 T6W 1
T3E T7E 1
T3IC 2 T7W 1
T4AD TICU 1
T6W 1 W5E 1
T7E 1 W5N 2
T7W 1
T8E
T8W
T9W
TICU 1
W5E 1
W5N 2
W7N


"John C" wrote:

I think you want this.
=SUMIF($C$2:$C$100,A2,$D$2:$D$100)
copy down for each unit listed in column A, expand the range beyond 100, or
shorten it up as needed.
Was confused by your comment: ...have each cell in "A" check all cells in
"B" to see...
I think you are checking column C, as you state in the next sentence.
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"khicks4u" wrote:

I have four columns (A, b, c, d). What could I do to have each cell in "A"
check all cells in "B" to see if their is a match. If there is a match in
"C" take the value in "D" and place it in "B" next to the match in column "A".
Unit(A) #of Admissions(B) Unit(C) #of
admissions(D)
CCU 5 TICU
2
ICU CCU
5
TICU ICU
4

Goal: I want to populate "B" with the information from "D" next to the
correct unit.

Thank you,
Kem

Excel Spreadsheet
 

Thanks for the feedback :)
--
** John C **


"khicks4u" wrote:

I will definately try this. Thank you so much for taking the time to help us
out.
Sincerely
Kem

"John C" wrote:

Okay, didn't know about the blanks. If there are only single occurances of
each clinic in column C (i.e.: only 1 T2, only 1 T3IC, etc), then you should
be able to use this formula:
=IF(ISNA(VLOOKUP(A2,$C$2:$D$100,2,FALSE)),"",VLOOK UP(A2,$C$2:$D$100,2,FALSE))

If, however, there may be multiple occurances of the same clinic in column
C, then you need to sum them up, (i.e.: T2=4, and T2=6, so you would want the
T2=10 for column A:B, you can use my original formula, but slightly modified:
=IF(SUMIF($C$2:$C$100,A2,$D$2:$D$100)=0,"",SUMIF($ C$2:$C$100,A2,$D$2:$D$100))
--
** John C **



"khicks4u" wrote:

Thank you John for your help. I do not think I explained myself correctly.
What I want to do is see if there is a match for the cells in column "A" in
Column"C". If there is a match I want to take the value in column "D" for
the match in column "C" and enter it in column "B" next to the match in
column "A". For example there is not a match in column "C" for CVIC so the
cell in Column "B" would stay empty. There is a match for CCU1 in column "C"
so the value of "1" would then be entered in the cooresponding cell in column
"B"
CCU1 1 CCU1 1
CTUN 1 CTUN 1
CTUS CTUS 3
CVIC CVSD 1
CVSD 1 NSIC 2
NSIC 2 NULL 5
NULL 5 O4 1
O4 1 PCCU 2
O5E T10E 2
PCCU 2 T10W 1
T10E 2 T2 3
T10W 1 T3IC 2
T2 3 T6W 1
T3E T7E 1
T3IC 2 T7W 1
T4AD TICU 1
T6W 1 W5E 1
T7E 1 W5N 2
T7W 1
T8E
T8W
T9W
TICU 1
W5E 1
W5N 2
W7N


"John C" wrote:

I think you want this.
=SUMIF($C$2:$C$100,A2,$D$2:$D$100)
copy down for each unit listed in column A, expand the range beyond 100, or
shorten it up as needed.
Was confused by your comment: ...have each cell in "A" check all cells in
"B" to see...
I think you are checking column C, as you state in the next sentence.
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"khicks4u" wrote:

I have four columns (A, b, c, d). What could I do to have each cell in "A"
check all cells in "B" to see if their is a match. If there is a match in
"C" take the value in "D" and place it in "B" next to the match in column "A".
Unit(A) #of Admissions(B) Unit(C) #of
admissions(D)
CCU 5 TICU
2
ICU CCU
5
TICU ICU
4

Goal: I want to populate "B" with the information from "D" next to the
correct unit.

Thank you,
Kem