extract dates

[doriannjeshi]

Hello,
I have a very long list of dates, 3500 rows in these format:

Jan-00	Feb-00	Mar-00	Apr-00	May-00	Jun-00
02 21:26 Aqu	01 07:48 Pis	02 18:03 Ari	01 05:01 Tau	02 22:24 Can	01 15:44 Leo
04 22:09 Pis	03 07:38 Ari	04 18:25 Tau	03 07:14 Gem	05 07:00 Leo	04 02:34 Vir
06 23:45 Ari	05 09:42 Tau	06 22:05 Gem	05 13:16 Can	07 18:35 Vir	06 14:59 Lib
09 03:26 Tau	07 15:07 Gem	09 05:45 Can	07 23:10 Leo	10 07:09 Lib	09 02:45 Sco
11 09:36 Gem	09 23:50 Can	11 16:38 Leo	10 11:24 Vir	12 18:41 Sco	11 12:05 Sag
13 18:06 Can	12 10:49 Leo	14 05:04 Vir	13 00:01 Lib	15 04:07 Sag	13 18:31 Cap
16 04:31 Leo	14 23:00 Vir	16 17:39 Lib	15 11:38 Sco	17 11:19 Cap	15 22:38 Aqu
18 16:27 Vir	17 11:36 Lib	19 05:34 Sco	17 21:38 Sag	19 16:30 Aqu	18 01:27 Pis
21 05:07 Lib	19 23:44 Sco	21 16:03 Sag	20 05:36 Cap	21 20:01 Pis	20 03:57 Ari
23 16:54 Sco	22 09:53 Sag	23 23:57 Cap	22 11:05 Aqu	23 22:21 Ari	22 06:53 Tau
26 01:50 Sag	24 16:32 Cap	26 04:25 Aqu	24 13:59 Pis	26 00:20 Tau	24 10:52 Gem
28 06:47 Cap	26 19:16 Aqu	28 05:42 Pis	26 15:00 Ari	28 03:06 Gem	26 16:28 Can
30 08:14 Aqu	28 19:05 Pis	30 05:13 Ari	28 15:34 Tau	30 07:55 Can	29 00:18 Leo
			30 17:30 Gem
Jul-00	Aug-00	Sep-00	Oct-00	Nov-00	Dec-00
01 10:42 Vir	02 19:08 Sco	01 13:48 Sag	01 05:56 Cap	01 23:06 Pis	01 08:22 Ari
03 22:59 Lib	05 06:00 Sag	03 22:27 Cap	03 12:03 Aqu	04 00:27 Ari	03 10:01 Tau
06 11:11 Sco	07 13:14 Cap	06 02:53 Aqu	05 14:22 Pis	06 00:25 Tau	05 11:27 Gem
08 21:04 Sag	09 16:32 Aqu	08 03:47 Pis	07 14:07 Ari	08 00:50 Gem	07 14:04 Can
11 03:27 Cap	11 17:11 Pis	10 03:00 Ari	09 13:17 Tau	10 03:32 Can	09 19:18 Leo
13 06:41 Aqu	13 17:09 Ari	12 02:45 Tau	11 14:02 Gem	12 09:48 Leo	12 04:03 Vir
15 08:12 Pis	15 18:24 Tau	14 04:57 Gem	13 18:01 Can	14 19:47 Vir	14 15:48 Lib
17 09:38 Ari	17 22:14 Gem	16 10:39 Can	16 01:53 Leo	17 08:08 Lib	17 04:33 Sco
19 12:16 Tau	20 04:56 Can	18 19:38 Leo	18 12:51 Vir	19 20:48 Sco	19 15:53 Sag
21 16:48 Gem	22 14:02 Leo	21 06:53 Vir	21 01:24 Lib	22 08:08 Sag	22 00:32 Cap
23 23:20 Can	25 00:57 Vir	23 19:19 Lib	23 14:04 Sco	24 17:25 Cap	24 06:34 Aqu
26 07:49 Leo	27 13:12 Lib	26 08:06 Sco	26 01:50 Sag	27 00:30 Aqu	26 10:46 Pis
28 18:17 Vir	30 02:03 Sco	28 20:10 Sag	28 11:47 Cap	29 05:23 Pis	28 14:02 Ari
31 06:29 Lib			30 19:01 Aqu		30 16:55 Tau

I need to fix the data in a single column

02 21:26 Aqu	1/2/1900 21:26	Aqu
04 22:09 Pis	1/4/1900 3:07	Pis
06 23:45 Ari	1/6/1900 6:23	Ari
09 03:26 Tau	1/9/1900 6:23	Tau

I am getting this data from a site:

Moon Sign Chart - Witches Lore

To find your Moon sign simply look up your year of birth and your month of birth, then you will know which sign of the Zodiac the Moon was in

witcheslore.com

 

[doriannjeshi]

will try this with Excel version 365 then
Thank you very much Dave !

 

[Dave Patton]

Try a combination of Vstack and Tocol
Advantage. Do not need to sort the data
Format the Date/Time to your preference
Experiment with more ranges
What years do you require? Do you need all 1900 - 2022?
Challenge how to repeat this ~ 125 times

Column A was created with
=VSTACK(TOCOL(Data!A5:F18,1,1),TOCOL(Data!A20:F34,1,1))

Astrology.xlsm
	A	B	C
1	1	1
2	02 21:26 Aqu	02-Jan-00 21:26	Aqu
3	04 22:09 Pis	04-Jan-00 22:09	Pis
4	06 23:45 Ari	06-Jan-00 23:45	Ari
5	09 03:26 Tau	09-Jan-00 03:26	Tau
6	11 09:36 Gem	11-Jan-00 09:36	Gem
2b

Cell Formulas
Range	Formula
A1:A171	A1	=VSTACK(TOCOL(Data!A5:F18,1,1),TOCOL(Data!A20:F34,1,1))
B1	B1	=A1
B2:B6	B2	=IF(ISNUMBER(A2),A2,INT(B1)-DAY(B1)+LEFT(A2,2)+MID(A2,4,5))
C2:C6	C2	=IF(ISNUMBER(A2),0,RIGHT(A2,3))
Dynamic array formulas.

 

[Dave Patton]

Good news
I tried the formula with 5 years of data and it worked fine.
It generated 1041 rows. N.B. one cell in the source data was corrupt and required a minor edit.

Do you need all years from 1900?
To make the formula more concise, you could try naming each range.

=VSTACK(TOCOL(A2:F16,1,1),TOCOL(A17:F31,1,1),TOCOL(A32:F47,1,1),TOCOL(A48:F62,1,1),TOCOL(A63:F78,1,1),TOCOL(A79:F93,1,1),TOCOL(A94:F109,1,1),TOCOL(A110:F124,1,1),TOCOL(A125:F140,1,1),TOCOL(A141:F155,1,1),TOCOL(A156:F171,1,1),TOCOL(A172:F186,1,1))

 

[Dave Patton]

Did you try naming the ranges?

=VSTACK(TOCOL(r_1,1,1),TOCOL(r_2,1,1),TOCOL(r_3,1,1))

There are examples of reorganizing data on this forum with formulas or with Power Query (Get and Transform Data). There was at least one thread today Dec 2.
" Change row data to one column"
Did you review that thread?

 

[Dave Patton]

No feedback or answers!
I guess project was completed.
The suggestion worked with at least 11 years of data.
Did you try with data for 10, 15, 20, ... years?