Calculating available hotel rooms

[Declamatory]

Good morning,

I have a download table. For each date the right hand column represents the number of rooms available and the left hand number represents take up.

On a separate tab I need to be able to show how many rooms are still has left on each date for each room type.

Can anybody help me please.

Thanks

[TABLE="width: 1036"]
<tbody>[TR]
[TD]Room Type[/TD]
[TD="align: right"]19/10/2019[/TD]
[TD="align: right"] 20/10/2019[/TD]
[TD="align: right"] 21/10/2019[/TD]
[TD="align: right"]22/10/2019[/TD]
[TD="align: right"] 23/10/2019[/TD]
[TD="align: right"]24/10/2019[/TD]
[TD="align: right"]25/10/2019[/TD]
[TD="align: right"] 26/10/2019[/TD]
[TD="align: right"]27/10/2019[/TD]
[TD="align: right"] 28/10/2019[/TD]
[TD] Total[/TD]
[/TR]
[TR]
[TD]Allocated Shared Accommodation[/TD]
[TD]'0/20'[/TD]
[TD]'0/100'[/TD]
[TD]'0/170'[/TD]
[TD]'3/220'[/TD]
[TD]'3/220'[/TD]
[TD]'3/220'[/TD]
[TD]'3/220'[/TD]
[TD]'0/60'[/TD]
[TD]'0/10'[/TD]
[TD]'0/0'[/TD]
[TD]'12/1,240'[/TD]
[/TR]
[TR]
[TD]Single Occupancy Room[/TD]
[TD]'0/80'[/TD]
[TD]'0/170'[/TD]
[TD]'1/350'[/TD]
[TD]'11/490'[/TD]
[TD]'11/490'[/TD]
[TD]'11/490'[/TD]
[TD]'11/490'[/TD]
[TD]'1/200'[/TD]
[TD]'0/20'[/TD]
[TD]'0/0'[/TD]
[TD]'46/2,780'[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]'0/100'[/TD]
[TD]'0/270'[/TD]
[TD]'1/520'[/TD]
[TD]'14/710'[/TD]
[TD]'14/710'[/TD]
[TD]'14/710'[/TD]
[TD]'14/710'[/TD]
[TD]'1/260'[/TD]
[TD]'0/30'[/TD]
[TD]'0/0'[/TD]
[TD]'58/4,020'[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

this works with the ' removed from the data table (e.g. Find and Replace All)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Room Type	19/10/2019	20/10/2019	21/10/2019	22/10/2019	23/10/2019	24/10/2019	25/10/2019	26/10/2019	27/10/2019	28/10/2019	Total
2	Allocated Shared Accommodation	0/20	0/100	0/170	3/220	3/220	3/220	3/220	0/60	0/10	0/0	12/1,240
3	Single Occupancy Room	0/80	0/170	1/350	11/490	11/490	11/490	11/490	1/200	0/20	0/0	46/2,780
4	Total	0/100	0/270	1/520	14/710	14/710	14/710	14/710	1/260
5
6	Room Type	19/10/2019	20/10/2019	21/10/2019	22/10/2019	23/10/2019	24/10/2019	25/10/2019	26/10/2019	27/10/2019	28/10/2019
7	Allocated Shared Accommodation	20	100	170	217	217	217	217	60	10	0	1228
8	Single Occupancy Room	80	170	349	479	479	479	479	199	20	0	2734
9	Total	100	270	519	696	696	696	696	259
Sheet3

Cell Formulas
Range	Formula
B7	=RIGHT(SUBSTITUTE(B2,"/",REPT(" ",50)),50)-LEFT(SUBSTITUTE(B2,"/",REPT(" ",50)),50)

 

[jmacleary]

Hi there. Set up your separate tab with the same layout as the source(Room Types in first column (column A), dates in first row(row 1). Then put this formula in cell B2 and drag across and down:

=-MID(LEFT(Sheet1!B2,FIND("/",Sheet1!B2)-1),2,2)+MID(Sheet1!B2,FIND("/",Sheet1!B2)+1,LEN(Sheet1!B2)-FIND("/",Sheet1!B2)-1)

NB This will only work if your data is exactly as you show, it assumes a single character at the beginning and end of each cell (the single quotes), and a single slash separating the 2 values.

 

[Declamatory]

That's perfect.

Thank you for your time.

 

[AlanY]

just curious, which solution you'd ended up with?

 

[Declamatory]

Hi Alan,

The second solution meant that I didn't have to do the find and replace. Both worked but the second solution means I can just paste the download into sheet 1.

Thanks

 

[AlanY]

thanks for the feedback