Creating a list out of dates/times table

**ItzikV** · 07-07-2016, 06:54 AM

Hello everyone,

I'm new here so I hope my post is according to the rules.

I have an excel sheet that looks something lie this:

date date date date
10:00 10:00 10:00 10:00
ms.*** name name name
ms.*** name name name
ms.*** name name name

date date date date
13:00 13:00 13:00 13:00
ms.*** name name name
ms.*** name name name
ms.*** name name name

The file is attached.
Each name is written 8 times in different date and hour.

I would like to find a quick way to get a list (preferrably a text file) that will contain the dates and hours for each person individually.
for example:
miss nelly
22/7 10:00
25/7 15:30
3/8 15:30
5/8 15:30
11/8 10:00
and three more.
any ideas? any help will be much appreciated.

**dflak** · 07-07-2016, 05:17 PM

From an American perspective the dates are backwards and so are the columns

. Nonetheless I think I can give this a shot.

Some questions first:

Will the dates always be in row 2? Will the time lines always be in rows 3, 10 and 18? What is the maximum number of names in the last group of names (under 16:30)?

Basically, I would write vba code to move along the columns, then get the date, a time and the names under that time and write them to a "flattened table" with Date : Time : Name. Then use this table as a data source for a pivot table. So it comes out looking like what you want.

**ItzikV** · 07-08-2016, 06:35 AM

Originally Posted by dflak

From an American perspective the dates are backwards and so are the columns

. Nonetheless I think I can give this a shot.

Some questions first:

Will the dates always be in row 2? Will the time lines always be in rows 3, 10 and 18? What is the maximum number of names in the last group of names (under 16:30)?

Basically, I would write vba code to move along the columns, then get the date, a time and the names under that time and write them to a "flattened table" with Date : Time : Name. Then use this table as a data source for a pivot table. So it comes out looking like what you want.

Thank you very much for your time and will.
About you questions. The dates will always be in row 2, the time lines will always by in rows 3, 10 and 17. And Under each group can contain up to six people.

Maybe an explanation about what you're seeing will help:
The sheet represents a schedule for a Math course I'm running for high school student during the summer.
Each day (on the dates you see on row 2) 3 groups of student are scheduled to arrive. Each group in the time line you see on rows 3, 10 and 17.

My final goal is to summerize when each student should arruve so I can text/mail them the dates instead of sending them the all sheet.

Is that information helpful?

**dflak** · 07-08-2016, 10:32 AM

Excel and I are stymied by the language barrier. I have to tell the code what sheets to reference. So I right clicked on the sheet and clicked on rename which highlighted the name of the sheet. Then I copied it and pasted it into the code. It did not translate. This is what I got, and my English version of Excel can't handle it. I suppose with the right regional settings it could.

Please Login or Register  to view this content.

I might do OK with English versions of the sheet names, but then I don't know how the strings will be handled.

**dflak** · 07-08-2016, 12:16 PM

As it turns out, English Excel will handle Hebrew text as data. Since I can't use your workbook, you will have to make your workbook look like the attachment. I've included a document on how to do that.

This has been a fun project. I'm learning a lot working internationally. In another case, I was working with someone from the Netherlands and I needed to format a date with a 4-digit year. In English this is =Text(Date,"yyyy"), however, in Dutch, year is jaar, so the formula becomes =Text(Date,"jjjj")

.