Comparing value to master coloumn
Solved/Closed
albatros

rizvisa1
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
Related:
 Comparing value to master coloumn
 Compare 2 coloumn with 2 column ✓  Forum  Office Software
 How to compare two coloumns in excel ✓  Forum  Excel
 Compare two worksheets and paste differences to another sheet  excel vba free download ✓  Forum  Excel
 Pokemon fire red master ball cheat  Guide
 Anvi folder locker master password  HowTo  Software
7 replies
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
Could you please put some some sort a separator between the columns, as in the first i see 3 col headers and 3 col values, and in the bottom i see two column.
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
Ok this is how I understood your workbook,which sorry still confuses me. See what I did below helps you out (after~~~~~~~~~~)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sheet 1
Master Sheet
you can use this formula at the intersection of Expected_tmc and Tuesday
=INDEX(Master!$B$2:$C$4, MATCH($A2, Master!$A$2:$A$4 ,0), MATCH(D$1, Master!$B$1:$C$1, 0))
you can use this formula at the intersection of Expected_WBC and Tuesday
=INDEX(Master!$B$2:$C$4, MATCH($A2, Master!$A$2:$A$4, 0),MATCH(E$1, Master!$B$1:$C$1, 0))
Simple drag down to fill in the rest of the columns
Day TMC WBC Tuesday 12 3 Wednesday 9 2 Thursday 8 4
TMC WBC Tuesday 10 4 Wednesday 5 3 Thursday 4 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sheet 1
A B C D E 1 Day TMC WBC Expected_TMC Expected_WBC 2 Tuesday 12 3 3 Wednesday 9 2 4 Thursday 8 4
Master Sheet
A B C 1 Expected_TMC Expected_WBC 2 Tuesday 10 4 3 Wednesday 5 3 4 Thursday 4 2
you can use this formula at the intersection of Expected_tmc and Tuesday
=INDEX(Master!$B$2:$C$4, MATCH($A2, Master!$A$2:$A$4 ,0), MATCH(D$1, Master!$B$1:$C$1, 0))
you can use this formula at the intersection of Expected_WBC and Tuesday
=INDEX(Master!$B$2:$C$4, MATCH($A2, Master!$A$2:$A$4, 0),MATCH(E$1, Master!$B$1:$C$1, 0))
Simple drag down to fill in the rest of the columns
albatros
Hello,
I tried your formula and it gives me error. I guess i am not able to explain you correctly the scenario.
How can I attach the excel sheet here? Or would be Ok if i can send you the excel sheet , it is very small in size 37KB only in size. Please let me know.
Waiting for response. Thanks again
I tried your formula and it gives me error. I guess i am not able to explain you correctly the scenario.
How can I attach the excel sheet here? Or would be Ok if i can send you the excel sheet , it is very small in size 37KB only in size. Please let me know.
Waiting for response. Thanks again
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
email me as rizvisa@hotmail.com
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
Do two things
Here is the formula for cell d2
=IF(ISERROR(VLOOKUP(TEXT(B2,"dddd"),MASTER!$A$1:$J$6,2,FALSE)), "",VLOOKUP(TEXT(B2,"dddd"),MASTER!$A$1:$J$6,2,FALSE))
Also change d1 on master to this AnnapolisExpected
you have do same for rest too
Here is what u need to change for each different city
Change #1 for each city : where is says "2, false" in the formula. right now "2" refers to column #2 where "AnnapolisExpected" on master. you have to change this number to the column where other cities are eg. 5 for RockvilleExpected
Here is the formula for cell d2
=IF(ISERROR(VLOOKUP(TEXT(B2,"dddd"),MASTER!$A$1:$J$6,2,FALSE)), "",VLOOKUP(TEXT(B2,"dddd"),MASTER!$A$1:$J$6,2,FALSE))
Also change d1 on master to this AnnapolisExpected
you have do same for rest too
Here is what u need to change for each different city
Change #1 for each city : where is says "2, false" in the formula. right now "2" refers to column #2 where "AnnapolisExpected" on master. you have to change this number to the column where other cities are eg. 5 for RockvilleExpected
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
What you are getting ?
I am getting 11. If I look at master for tuesday for that city it is 11.
I am getting 11. If I look at master for tuesday for that city it is 11.
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
Ok I just emailed your workbook too.
1) There is first coloumn for day( which has value from monday to saturday) , Second coloumn is total appointment made for location A, Third coloumn is expected appointments.
2) I need a formula which fills value on the third coloumn from Master Appointment list
3) Master Appointment has two coloumns locations ( vertical) and Day( horizontal)
So basically formula should be if the location is X and Day is Y then value in expected coloumn should from Master Appointment list.
Please suggest i would be really appreciate it. I can send the excel sheet example if needed.