Looking for ideas on how to combat a new task.
Raw Data:
Column 1: Date
Column 2: Folio # (Reservation #)
Column 3: Price
Eg.
1/1/14 65451 $60.00
1/1/14 54874 $60.00
1/1/14 78451 $70.00
1/1/14 45612 $60.00
1/2/14 87451 $80.00
1/2/14 98745 $70.00
1/2/14 12346 $80.00
1/2/14 65497 $80.00
etc.
Usually there are multiple dates for each day in the month, followed by a folio number, and the price for that folio.
In another table i need to combine all the dates into one line followed by the median for each date.
Eg.
1/1/14 $60.00
1/2/14 $80.00
etc. I am thinking I can use a if or vlookup or something, and then use a median function to plug in the prices for each date, but I'm not sure of an exact formula that might work.
In another table I need to copy my raw data to another tab/table and organize it by folio. I was hoping someone might know a macro that I could use.
Eg.
<TBODY>
</TBODY>
The thing is when I copy the data, I need to do a couple of things with it.
The date is not really important, most likely I would keep the most recent date for each folio.
Next I need a function to add up the mode (count) for each folio. Then I need a function to take an average for each folio price.
Eg.
<TBODY>
</TBODY>
This would be the data after. Notice that 56577 showed up twice, so the third column shows (2) and the forth column shows the average. ($174.96 + $165.68 / 2 = $340.64)
Any ideas? Appreciate any thoughts. Thank you.
Raw Data:
Column 1: Date
Column 2: Folio # (Reservation #)
Column 3: Price
Eg.
1/1/14 65451 $60.00
1/1/14 54874 $60.00
1/1/14 78451 $70.00
1/1/14 45612 $60.00
1/2/14 87451 $80.00
1/2/14 98745 $70.00
1/2/14 12346 $80.00
1/2/14 65497 $80.00
etc.
Usually there are multiple dates for each day in the month, followed by a folio number, and the price for that folio.
In another table i need to combine all the dates into one line followed by the median for each date.
Eg.
1/1/14 $60.00
1/2/14 $80.00
etc. I am thinking I can use a if or vlookup or something, and then use a median function to plug in the prices for each date, but I'm not sure of an exact formula that might work.
In another table I need to copy my raw data to another tab/table and organize it by folio. I was hoping someone might know a macro that I could use.
Eg.
| 16-Jan</SPAN> | 56577</SPAN> | $174.96</SPAN> | ||
| 17-Jan</SPAN> | 56577</SPAN> | $165.68</SPAN> | ||
| 17-Jan</SPAN> | 57125</SPAN> | $165.68</SPAN> | ||
| 13-Jan</SPAN> | 57126</SPAN> | $129.29</SPAN> | ||
| 17-Jan</SPAN> | 57708</SPAN> | $165.68</SPAN> | ||
| 13-Jan</SPAN> | 58116</SPAN> | $129.29</SPAN> | ||
| 20-Jan</SPAN> | 58133</SPAN> | $179.04</SPAN> | ||
| 21-Jan</SPAN> | 58133</SPAN> | $179.47</SPAN> | ||
| 18-Jan</SPAN> | 59211</SPAN> | $166.19</SPAN> |
<TBODY>
</TBODY>
The thing is when I copy the data, I need to do a couple of things with it.
The date is not really important, most likely I would keep the most recent date for each folio.
Next I need a function to add up the mode (count) for each folio. Then I need a function to take an average for each folio price.
Eg.
| 16-Jan</SPAN> | 56577</SPAN> | 2</SPAN> | $340.64</SPAN> | |
| 17-Jan</SPAN> | 57125</SPAN> | 1</SPAN> | $165.68</SPAN> | |
| 13-Jan</SPAN> | 57126</SPAN> | 1</SPAN> | $129.29</SPAN> | |
| 17-Jan</SPAN> | 57708</SPAN> | 1</SPAN> | $165.68</SPAN> | |
| 13-Jan</SPAN> | 58116</SPAN> | 1</SPAN> | $129.29</SPAN> | |
| 20-Jan</SPAN> | 58133</SPAN> | 2</SPAN> | $358.51</SPAN> | |
| 18-Jan</SPAN> | 59211</SPAN> | 1</SPAN> | $166.19</SPAN> |
<TBODY>
</TBODY>
This would be the data after. Notice that 56577 showed up twice, so the third column shows (2) and the forth column shows the average. ($174.96 + $165.68 / 2 = $340.64)
Any ideas? Appreciate any thoughts. Thank you.
.
