1

I have a table of attendance which look like this,

  Name | Date      | Time        |
  abc  | 12/3/2018 | 12:36:11 PM |
  abc  | 12/3/2018 | 08:30:11 PM |
  EFG  | 13/3/2018 | 09:35:11 PM |
  EFG  | 13/3/2018 | 06:23:11 PM |
  EFG  | 13/3/2018 |  9:36:11 PM |
  abc  | 14/3/2018 | 11:36:11 PM |
  GHT  | 14/3/2018 |  3:36:11 PM |
  GHT  | 14/3/2018 | 01:36:11 PM |
  abc  | 15/3/2018 | 10:36:11 PM |

What I am trying to do is something like this: get the name and the date (if both duplicates) and, if the name and the date same, need to get the different time to a new cell.

  Name | Date      | Time        | new cell    | new cell    |
  abc  | 12/3/2018 | 12:36:11 PM | 08:30:11 PM |
  EFG  | 13/3/2018 | 09:35:11 PM | 06:23:11 PM |  9:36:11 PM |
  abc  | 14/3/2018 | 11:36:11 PM |
  GHT  | 14/3/2018 |  3:36:11 PM | 01:36:11 PM |
  abc  | 15/3/2018 | 10:36:11 PM |

So far I tried merge two rows in excel worksheet where one cell has same content but other cells have different content have different content which is the only solution I came close.

and also tried to export it to Access and write a SQL query to get the above result.  How can I achieve this?

6
  • 1
    I works guess this is a job for VBa but you specifically tagged worksheet function
    – Dave
    Feb 5, 2019 at 6:15
  • 1
    Is there a maximum possible number of records for a name+date? Are they always sequenced?
    – fixer1234
    Feb 5, 2019 at 6:34
  • Power Query will be a better choose.
    – Lee
    Feb 5, 2019 at 7:25
  • 1
    D2=IF(A3=A2,C3,"") E2=IF(AND(A4=A2,D2<>""),C4,"") F2=IF(AND(A5=A2,E2<>""),C5,"") and so on...
    – Akina
    Feb 5, 2019 at 7:44
  • @fixer1234 Normally,it should be 2 occurrence,Bur sometime an employee can put the finger two -three times accidentally.but if that happen we can count only 2 occurrence and neglect any more records.
    – maxasela
    Feb 6, 2019 at 6:05

1 Answer 1

0

This is not hard to do with worksheet formulas.  I assume that your existing “Name”, “Date” and “Time” columns are Columns A, B and C.  And I’ll assume that you want to project the data onto a new sheet; i.e., that you have the data in your first illustration on Sheet1, and you want the layout of your second illustration on Sheet2.  Projecting it to another region on the same sheet is the same except for the details of the formulas.  In either case, if you want to replace the original data, you can copy and paste values.

Pick three columns on Sheet1 that you aren’t using; say, X, Y and Z.

  • Ensure that cell X1 is blank (or enter 0 there).
  • In cell X2, enter =IF(AND(A1=A2,B1=B2),X1,X1+1).
  • In cell Y2, enter =MATCH(ROW()-1,X:X,0).
  • In cell Z2, enter =COUNTIF(X:X,ROW()-1).
  • Select X2:Z2 and drag/fill down (at least) as far as you have data.

On Sheet2,

  • Enter column headings as desired in Row 1.
  • In cell A2, enter =IFERROR(INDEX(Sheet1!A:A,Sheet1!Y2),"").
  • In cell B2, enter =IFERROR(INDEX(Sheet1!B:B,Sheet1!Y2),"").
  • In cell C2, enter =IF((COLUMN()-COLUMN($C:$C))<Sheet1!$Z2, INDEX(Sheet1!$C:$C,Sheet1!$Y2+(COLUMN()-COLUMN($C:$C))),"").
  • Select C2 and drag/fill to the right (at least) far enough to cover your data (i.e., the maximum number of duplicate rows you have for any Name / Date pair).
  • Select the filled cells in Row 2 (i.e., A2 through the cell you dragged to in the previous step) and drag/fill down (at least) far enough to cover your data (i.e., the number of unique Name / Date pairs you have).

Voilà

Sheet1:

       Sheet1

Sheet2:

       Sheet2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .