Get 1st and Last Row from range

[markusvirus]

Hi Experts,

I have a spreadsheet which has several rows that shows a person's activity. and there are several person that is on the list. what I'm trying to achieve is to get only the 1st and last row for each person and transfer them to another sheet/tab. is this possible in excel through vba? I attached a sample sheet for reference, thanks!

[sweep]

Hi,

On the sheet "tosheet" in cell B2, enter this formula;

Please Login or Register  to view this content.

This can be copied to cell B4, and across the rows to C2:H2 and C4:H4

in cell B3 of "tosheet" enter the following ARRAY formula (use CTRL, SHIFT and ENTER to confirm)

Please Login or Register  to view this content.

This can be copied to cell B5, and across the rows to C3:H3 and C5:H5

[jindon]

vba

Please Login or Register  to view this content.

[TMS]

You could use an Advanced Filter to get a list of person names, if you don't already have a list, and then loop through that using Match to get the first row and Match plus COUNTIF to get the last row

[markusvirus]

thanks so much guys!! I used jindon's codes so I could just have it added to the vba script.

one more thing, so now I get 2 rows per person in sheet2, is it possible to capture the person's name, the earliest start time and the latest end time?

[markusvirus]

thanks so much guys!! I used jindon's codes so I could just have it added to the vba script.

one more thing, so now I get 2 rows per person in sheet2, is it possible to capture the person's name, the earliest start time and the latest end time?

[jindon]

If the dates (col.C) are always the same,

Please Login or Register  to view this content.

[markusvirus]

hi jindon, thanks so much for spending time to answer my question. I'm sorry for the confusion.

Person Task Name Date Start Time End Time Total
PersonA task 01/01/17 9:00 AM 10:00 AM
PersonA task 01/01/17 5:34 PM 6:00 PM

from the example. I would want to have the duration from "9:00 AM" start time to "6:00PM" end time. I am challenged because there are 2 rows per person in my file

[jindon]

You don't have such data in your file.

Post a workbook with the data and the EXACT results that you want clearly showing the logic behind it.

[markusvirus]

Please see "tosheet" from attached file, thank you so much!

[jindon]

No, I don't understand the logic.
Earliest Start Time for 0001 PersonA is on row 6, 09:19:52 AM
Latest End Time is on row 3, 09:19:52 AM

This is based on what you said

one more thing, so now I get 2 rows per person in sheet2, is it possible to capture the person's name, the earliest start time and the latest end time?

[markusvirus]

under "tosheet", person A's earliest start time is in D2 and the latest end time is in E3. Sorry if I confused you.

[markusvirus]

then the duration/outcome should be in column F, but since it involves 2 cells, 2 rows for that person, that's actually one of the next thing I have to figure out

[jindon]

Then what's wrong with my first code?

[markusvirus]

nothing's wrong with your first code, it actually does the job in getting only the earliest and latest entry per person from the "fromsheet" to the "tosheet". what I need is after the first code to transfer the data to the "tosheet", I want to calculate the duration from the earliest start time (E3-D2), then for the next person (E5-D4), next E7-D6, and so on.

[jindon]

Post a workbook with EXACT result that you want.
Manually calculate the cell, if needed.

I don't want to do a guess work to avoid more confusion.