Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Posting Excel spreadsheet to Google Spreadsheet using VBA | Excel Discussion (Misc queries) | |||
In Excel I want to copy text from spreadsheet to spreadsheet | Excel Worksheet Functions | |||
convert ms works spreadsheet to excel spreadsheet on pda | Excel Discussion (Misc queries) | |||
conversion of MS Works Spreadsheet to Excel 2002 Spreadsheet | Excel Discussion (Misc queries) | |||
Is there a way to insert a formula, password or macro in an excel spreadsheet that will automatically delete the spreadsheet? | Excel Discussion (Misc queries) |