|
Macro for comparing Hotel Prices
Hello,
I am getting crazy tryng to compare hotel prices between two given dates in a large sheet. I got some results with thousands of IF and Hlookup formulas. Do you have any listing that I may adapt for this use to have a macro calculating the amount column? It looks like: From Jun 20 to Sep 9 Date Jan1 Jun10 Aug30 Sep30 Dec31 Totals HotelA FB ฌ 50,00 80,00 90,00 75,00 60,00 4900 HotelA BB ฌ 40,00 50,00 70,00 55,00 50,00 3200 Date HotelB FB ฌ 00,00 70,00 90,00 70,00 00,00 4600 HotelB BB ฌ 00,00 00,00 00,00 00,00 00,00 0 Thank you for your help! -- dilettante (=beginner) |
Macro for comparing Hotel Prices
Hi
What do you mean by "compare"? What do you mean by "calculate the amount column"? Assuming the data you have given is the input, what do you want as an output? regards Paul dilettante wrote: Hello, I am getting crazy tryng to compare hotel prices between two given dates in a large sheet. I got some results with thousands of IF and Hlookup formulas. Do you have any listing that I may adapt for this use to have a macro calculating the amount column? It looks like: From Jun 20 to Sep 9 Date Jan1 Jun10 Aug30 Sep30 Dec31 Totals HotelA FB 50,00 80,00 90,00 75,00 60,00 4900 HotelA BB 40,00 50,00 70,00 55,00 50,00 3200 Date HotelB FB 00,00 70,00 90,00 70,00 00,00 4600 HotelB BB 00,00 00,00 00,00 00,00 00,00 0 Thank you for your help! -- dilettante (=beginner) |
Macro for comparing Hotel Prices
Hello Paul
I enter the start date and the end date in two cells and I would like to get the costs of overnights between the two entered dates in the cells of the column "Totals" Thank you for your attention to my problem! Regards -- dilettante " wrote: Hi What do you mean by "compare"? What do you mean by "calculate the amount column"? Assuming the data you have given is the input, what do you want as an output? regards Paul dilettante wrote: Hello, I am getting crazy tryng to compare hotel prices between two given dates in a large sheet. I got some results with thousands of IF and Hlookup formulas. Do you have any listing that I may adapt for this use to have a macro calculating the amount column? It looks like: From Jun 20 to Sep 9 Date Jan1 Jun10 Aug30 Sep30 Dec31 Totals HotelA FB ฌ 50,00 80,00 90,00 75,00 60,00 4900 HotelA BB ฌ 40,00 50,00 70,00 55,00 50,00 3200 Date HotelB FB ฌ 00,00 70,00 90,00 70,00 00,00 4600 HotelB BB ฌ 00,00 00,00 00,00 00,00 00,00 0 Thank you for your help! -- dilettante (=beginner) |
Macro for comparing Hotel Prices
Assuming your stay in and out dates are in A2 and B2,
your hotel rate start-dates are in C4:G4, and are the same for all hotels, your rates are in C5:G5 and continue down the sheet Enter a date later than the latest rate start date in H5. In C2, enter =MAX(MIN(D$4-$A2,D$4-C$4)-MAX(D$4-$B2,0),0), and drag to G2. This should produce the days of stay in each rate period above the rate period. In I5 enter =SUMPRODUCT($C$2:$G$2,C5:G5) and drag down as many rows as there are hotel stay types. You should now have the stay price for each hotel stay type in column I. Let me know if the rates change on different dates for different hotels, because this solution might be cumbersome in that case - you would need to create a unique row for each hotel similar to C2:G2 in the above solution. You may need something more elegant in that case. HTH Declan dilettante wrote: Hello Paul I enter the start date and the end date in two cells and I would like to get the costs of overnights between the two entered dates in the cells of the column "Totals" Thank you for your attention to my problem! Regards -- dilettante " wrote: Hi What do you mean by "compare"? What do you mean by "calculate the amount column"? Assuming the data you have given is the input, what do you want as an output? regards Paul dilettante wrote: Hello, I am getting crazy tryng to compare hotel prices between two given dates in a large sheet. I got some results with thousands of IF and Hlookup formulas. Do you have any listing that I may adapt for this use to have a macro calculating the amount column? It looks like: From Jun 20 to Sep 9 Date Jan1 Jun10 Aug30 Sep30 Dec31 Totals HotelA FB 50,00 80,00 90,00 75,00 60,00 4900 HotelA BB 40,00 50,00 70,00 55,00 50,00 3200 Date HotelB FB 00,00 70,00 90,00 70,00 00,00 4600 HotelB BB 00,00 00,00 00,00 00,00 00,00 0 Thank you for your help! -- dilettante (=beginner) |
Macro for comparing Hotel Prices
Dear Declan,
you are great! after weeks of my complicated formulas .... it works! Each Hotel has differen saison dates so now I just adapt the formula for each hotel. Some hotels are closed during different mounths but I solve the question just putting for all the beginning date as jan the 1st , the closing as dec 31st and the rates during close periods as 0. For this cases I will try to add a remark in column J to poit out that the hotel is not open for all the given รจ period. Thank you very much again for your help Dilettante Thank you very -- dilettante "DOR" wrote: Assuming your stay in and out dates are in A2 and B2, your hotel rate start-dates are in C4:G4, and are the same for all hotels, your rates are in C5:G5 and continue down the sheet Enter a date later than the latest rate start date in H5. In C2, enter =MAX(MIN(D$4-$A2,D$4-C$4)-MAX(D$4-$B2,0),0), and drag to G2. This should produce the days of stay in each rate period above the rate period. In I5 enter =SUMPRODUCT($C$2:$G$2,C5:G5) and drag down as many rows as there are hotel stay types. You should now have the stay price for each hotel stay type in column I. Let me know if the rates change on different dates for different hotels, because this solution might be cumbersome in that case - you would need to create a unique row for each hotel similar to C2:G2 in the above solution. You may need something more elegant in that case. HTH Declan dilettante wrote: Hello Paul I enter the start date and the end date in two cells and I would like to get the costs of overnights between the two entered dates in the cells of the column "Totals" Thank you for your attention to my problem! Regards -- dilettante " wrote: Hi What do you mean by "compare"? What do you mean by "calculate the amount column"? Assuming the data you have given is the input, what do you want as an output? regards Paul dilettante wrote: Hello, I am getting crazy tryng to compare hotel prices between two given dates in a large sheet. I got some results with thousands of IF and Hlookup formulas. Do you have any listing that I may adapt for this use to have a macro calculating the amount column? It looks like: From Jun 20 to Sep 9 Date Jan1 Jun10 Aug30 Sep30 Dec31 Totals HotelA FB ฌ 50,00 80,00 90,00 75,00 60,00 4900 HotelA BB ฌ 40,00 50,00 70,00 55,00 50,00 3200 Date HotelB FB ฌ 00,00 70,00 90,00 70,00 00,00 4600 HotelB BB ฌ 00,00 00,00 00,00 00,00 00,00 0 Thank you for your help! -- dilettante (=beginner) |
Macro for comparing Hotel Prices
You are welcome. I have a solution that deals with unique season dates
for each hotel, without adapting the formulas for each hotel, but I need to complete it later, as I am rushing out now. I will post it when it is complete, some time this evening (US time) or tomorrow morning. Declan dilettante wrote: Dear Declan, you are great! after weeks of my complicated formulas .... it works! Each Hotel has differen saison dates so now I just adapt the formula for each hotel. Some hotels are closed during different mounths but I solve the question just putting for all the beginning date as jan the 1st , the closing as dec 31st and the rates during close periods as 0. For this cases I will try to add a remark in column J to poit out that the hotel is not open for all the given ่ period. Thank you very much again for your help Dilettante Thank you very -- dilettante "DOR" wrote: Assuming your stay in and out dates are in A2 and B2, your hotel rate start-dates are in C4:G4, and are the same for all hotels, your rates are in C5:G5 and continue down the sheet Enter a date later than the latest rate start date in H5. In C2, enter =MAX(MIN(D$4-$A2,D$4-C$4)-MAX(D$4-$B2,0),0), and drag to G2. This should produce the days of stay in each rate period above the rate period. In I5 enter =SUMPRODUCT($C$2:$G$2,C5:G5) and drag down as many rows as there are hotel stay types. You should now have the stay price for each hotel stay type in column I. Let me know if the rates change on different dates for different hotels, because this solution might be cumbersome in that case - you would need to create a unique row for each hotel similar to C2:G2 in the above solution. You may need something more elegant in that case. HTH Declan dilettante wrote: Hello Paul I enter the start date and the end date in two cells and I would like to get the costs of overnights between the two entered dates in the cells of the column "Totals" Thank you for your attention to my problem! Regards -- dilettante " wrote: Hi What do you mean by "compare"? What do you mean by "calculate the amount column"? Assuming the data you have given is the input, what do you want as an output? regards Paul dilettante wrote: Hello, I am getting crazy tryng to compare hotel prices between two given dates in a large sheet. I got some results with thousands of IF and Hlookup formulas. Do you have any listing that I may adapt for this use to have a macro calculating the amount column? It looks like: From Jun 20 to Sep 9 Date Jan1 Jun10 Aug30 Sep30 Dec31 Totals HotelA FB 50,00 80,00 90,00 75,00 60,00 4900 HotelA BB 40,00 50,00 70,00 55,00 50,00 3200 Date HotelB FB 00,00 70,00 90,00 70,00 00,00 4600 HotelB BB 00,00 00,00 00,00 00,00 00,00 0 Thank you for your help! -- dilettante (=beginner) |
| All times are GMT +1. The time now is 10:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com