How to match a column of date times with a column of times (and corresponding data)?

**jbody** · 03-19-2016, 12:45 PM

I have attached a picture of part of the document I am tying to sort. There are about 4000 rows. The two time columns (A and K) and the corresponding data to the right of each I need to match up to the exact time if possible. The data in A is in a strange format, I don't know how to take out the dates and the T and Z without going through and doing each one individually. And then is it possible to align the rows with matching times? And just delete the rest.

Capture.PNG

**ExcelsBiggestFan** · 03-19-2016, 01:17 PM

I can't get the pic to open.

**jbody** · 03-19-2016, 01:31 PM

Capture.PNG

I have tried to attach it again.

**ExcelsBiggestFan** · 03-19-2016, 01:50 PM

Sorry, I still can't see. Could you put a sample spreadsheet up?

**Ochimus** · 03-19-2016, 02:12 PM

jbody,

Slightly confused, because you don't have a "date" element in Col K?. But - assuming the date for Col K is "implied" to match Col A - to convert Col A to your time without the letters, put this formula in cell O2 and copy down to last entry in COl A

=TIMEVALUE(MID(A2,12,8))

Format Col O as "time" - "hh:mm:ss". That will give you the time that you can compare to Col K (assuming K is an actual time - if not, repeat formula in another blank column.)

You can then sort by either Col K or Col O, and compare them with this in P2 copied down =K2=O2.

Will give you True or False in COl P. Delete all the ones that are "True", and you will be left with a list of "unique" times.

Ochimus

EDIT: Sorry, reread and realised you want to remove the unmatched times. Delete the rows saying "False"

**jbody** · 03-19-2016, 02:17 PM

OK hopefully this works...

**jbody** · 03-19-2016, 02:28 PM

Thanks very much Ochimus, will give it a go!

**Ochimus** · 03-19-2016, 02:40 PM

jbody,

Thanks for the attachment.

Col K was not a date, but text, so I have converted it on the attached.

The "time" in Col A is in Col Q, and the "time" in Col K is in Col P

Sorted the sheet by Col "K" time, and you can see in Col R whether the times match or not.

Formula was only carried down to row 2751, where Col A becomes blank.

Obviously you can copy the Cols O - Q down as far as you want.

Ochimus

How to match a column of date times with a column of times (and corresponding data)?

LinkBack

Thread Tools

Rate This Thread

Display

How to match a column of date times with a column of times (and corresponding data)?

Re: How to match a column of date times with a column of times (and corresponding data)?

Re: How to match a column of date times with a column of times (and corresponding data)?

Re: How to match a column of date times with a column of times (and corresponding data)?

Re: How to match a column of date times with a column of times (and corresponding data)?

Re: How to match a column of date times with a column of times (and corresponding data)?

Re: How to match a column of date times with a column of times (and corresponding data)?

Re: How to match a column of date times with a column of times (and corresponding data)?

Thread Information

Users Browsing this Thread

Similar Threads

Copy and Paste data in column A number of times displayed in column B

If a search term can be found 1 time, 2 times 3 times 4 times 5 times

Count num of times data appears in one column, depending upon criteria in another column

Copy column variable number of times between start date and end date

Sorting a mixture of times & 'times as text' in the same column

Find match in another column, compare times

Count number of times a date is over 3 years in a column

How to count the number of times a date appears in a column

Bookmarks

Bookmarks

Posting Permissions