Splitting a column with 2 values each below the other into 2 columns i.e account number and date

[N3il-585]

How do I do this, I have 4000 lines with account number on fist row and date on the following row but I would like these two things to be next to each other.

The complicating factor is that the date row is below the account number row but I wish to have it in the same row as the account number as the rest of the data in the subsequent columns are reflected in the the same row as the account number.

The reason is that I want all the data in a table so that I can pivot, but cutting the date out of a row and posting it to another row will be very laborious.

[#2]

@N3il-585 Assuming that this is a one time exercise, I'd suggest you insert a column to the right of the one where you have account number and date below one another and enter the following:

=OFFSET(A1,1,0)

Where A1 would be the cell where you have the account number. Copy it all the way down. Then copy/paste-values that new column on top of itself. Filter on dates in the first column and delete these rows. Clear the filter and you are left with the Account numbers in one column and dates in the next.

Practise on a copy of your live file, in case it goes wrong!

[#3]

@N3il-585 Hey you can try this assuming column 1 have cells blank where the date is in column 2

suppose your data is starting from first row then go to C1 and type this formula

=B2

and drag it till the end

then you apply filter to all data and go to A1 and filter data for blank

then delete all those rows and your data will be ready