MeraWindows Forums - Get your Computer Problems Fixed

This is a site run by the community - for the community. At the site, we share and contribute our computer learnings with each other. We discuss about Windows, Office, Mobile, Gaming and other computer related topics. At our forums you can post your queries and our very helpful community members will respond quickly to your queries. We organize lots of competitions from time to time and award prizes to the winners.
You will need to register at the site before posting in forums. It's absolutely free. After registering, you can get all the benefits available to our registered members, you can access our Downloads section, you can participate in contests, etc. You can post in forums in English as well as in Hindi, in fact we encourage you to use Hindi in your posts. If you have any problem with registration or login, please contact us.

Search Home All Forums

5/21/2012 6:44 PM

Prabhakara

Joined: 6/7/2011

Posts: 9

Travel consolidation

We need to consolidate the travel report, and incases where the travel is being extended multiple times, we need to know only the first date and last date. Please help.

							Result
Emp name	Initial tarvel		Extended(1)		Extended(2)		Actual Travel dates
	From	To	From	To	From	To	From	To
A	01/01/2012	20/01/2012	21/01/2012	31/01/2012	01/02/2012	10/02/2012	01/01/2012	10/02/2012
B	02/02/2012	10/02/2012	11/02/2012	21/02/2012	22/02/2012	31/03/2012	02/02/2012	31/03/2012
C	03/01/2012	25/01/2012	26/01/2012	15/02/2012	16/02/2012	14/04/2012	03/01/2012	14/04/2012

regards
sheeja

Regards, Prabhakara

5/21/2012 8:27 PM

Ashish Mathur

www.ashishmathur.com
Joined: 11/17/2006

Posts: 1686

Re: Travel consolidation

Hi,

Try this

Assuming dates are in range B3:G5, in cell H3, enter =B3 and copy down till cell B5. In cell I3, enter the following formula and copy down till cell I5

=LOOKUP(1E+100,$B3:$G3)

Hope this helps.

For Microsoft Excel trainings in India, please visit http://www.ashishmathur.com/corporate-interventions/

Regards,

Ashish Mathur -

Microsoft Excel MVP
Website
Blog
Follow @ExcelAshish

5/22/2012 9:43 AM

Prabhakara

Joined: 6/7/2011

Posts: 9

Re: Travel consolidation

Hi Ashish,

Sorry, I put my query in wrong way. Here is the raw data and the expected result, request you to provide the solution.

	Data
Emp name	Initial tarvel
	From	To
A	01/01/2012	20/01/2012
B	02/02/2012	10/02/2012
A	21/01/2012	31/01/2012
C	03/01/2012	25/01/2012
B	11/02/2012	21/02/2012
C	26/01/2012	15/02/2012
C	16/02/2012	14/04/2012
B	22/02/2012	31/03/2012
A	01/02/2012	10/02/2012
A	01/03/2012	31/03/2012
B	15/04/2012	27/04/2012
B	28/04/2012	31/05/2012

	Expected Result
	From	To
A	01/01/2012	10/02/2012	1st travel
B	02/02/2012	31/03/2012
C	03/01/2012	14/04/2012
A	01/03/2012	31/03/2012	2nd travel
B	15/04/2012	31/05/2012

Thanks,
Prabhakara

Regards, Prabhakara

5/22/2012 9:56 AM

Ashish Mathur

www.ashishmathur.com
Joined: 11/17/2006

Posts: 1686

Re: Travel consolidation

Hi,

I cannot undestand your expected result. Why should the first "To" date of A be 10/2/2012 and not 31/01/2012 or why not 30/3/2012? Clarify the "From" and "To" date for others as well.

For Microsoft Excel trainings in India, please visit http://www.ashishmathur.com/corporate-interventions/

Regards,

Ashish Mathur -

Microsoft Excel MVP
Website
Blog
Follow @ExcelAshish

5/22/2012 2:11 PM

Prabhakara

Joined: 6/7/2011

Posts: 9

Re: Travel consolidation

Hi,

Because it is the second Travel (Fresh travel) not an extension of first Travel. The first Travel will be closed on 10/2/2012(actual return date after considering the extension).

Thanks,
Prabhakara

Regards, Prabhakara

5/22/2012 7:22 PM

Ashish Mathur

www.ashishmathur.com
Joined: 11/17/2006

Posts: 1686

Re: Travel consolidation Modified By Ashish Mathur on 5/22/2012 7:25:54 PM

Hi,

Try this

1. Suppose your data is in range B5 : D16 (headings are in row 4)
2. Select range B4 : D16 and sort on Emp Name in ascending order
3. In cell E4, type Count
4. In cell E5, type 1
5. In cell E6, enter =IF(B6=B5,IF(C6-D5=1,E5,E5+1),E5+1) and copy down till cell E16
6. Select B4:E16 and create a pivot table
7. Drag the Count and Emp Name column to the row area of the pivot table
8. Drag From and To columns to the data area of the pivot table
9. Right click on any number under the From column in the data area of the pivot and select Value Field Settings > Min
10. Right click on any number under the From column in the data area of the pivot and select Number format > Date
11. Right click on any number under the To column in the data area of the pivot and select Value Field Settings > Max
12. Right click on any number under the To column in the data area of the pivot and select Number format > Date
13. Right click on any subtotal row and remove it
14. Right click on the word Grand Total in the pivot table and remove it

Hope this helps.

Let me know how it works?
Hope this helps.

For Microsoft Excel trainings in India, please visit http://www.ashishmathur.com/corporate-interventions/

Regards,

Ashish Mathur -

Microsoft Excel MVP
Website
Blog
Follow @ExcelAshish

Page 1 of 1