Combining Excel Data

[CA_Traveler]

I want to combine 2 sets of Excel data for the same time period and one was collected every second and the second one every minute.

How can I combine/merge the second set of data into the first first? The 59 seconds of the second set could all be blank after the merge.

Anyone have a macro, VB or other method to accomplish this?

[hawkeye-08]

I enjoy those lookup functions but some of them consume much time in large spreadsheets.

[Ductape]

Assuming you want to align these to chart them together... a simple Vlookup function formula as a new column alongside the solar controller data should do it. Fetch the USNO value where the USNO time = solar controller time.

That's *IF* what you have is real time values (a number). If it's text... same answer but you need the text formats to match.

[CA_Traveler]

Thanks for the ideas they are great - I just need some time to try them and get back to you guys and apologize for the delay.

The "every second" data is solar controller data collected every 5 seconds and in UTC time format. I use a formula to add a am/pm time column that humans can understand. Hopefully I could convert this column to values and sort the two sets of data. The second set of data is sun elevations from the USNO lab and it has a standard am/pm time scale. I have graphs of both data that could be combined. Just thought I'd let you know about the application.

It's been awhile since I've used Excel macros but that is a challenge going forward and I did ask and want those kinds of options.

Thanks to both of you for giving me options.

[Harvard]

Here is a VBA macro that expects the one minute data to be in Column A and the one second data in Column B and it pokes the results in Column C.

Option Explicit

Public Sub PopulateColumnC()
Dim Row As Integer
Dim m1 As Integer
Dim m2 As Integer
Dim y As Integer
Dim f As Single
Dim f1 As Single
Dim f2 As Single
Dim f3 As Single
Dim d As Single
Dim s As Single

With Sheet1
Row = 1
m1 = 1
m2 = 2
s = 0#
f3 = 0#

Do
f1 = .Cells(m1, 1).Value
f2 = .Cells(m2, 1).Value
If f2 <> 0# Then
d = (f2 - f1) / 60#
For y = 1 To 60
.Cells(Row, 3).Value = f3
f3 = f3 + d
Row = Row + 1
Next y
End If
m1 = m1 + 1
m2 = m2 + 1
Loop Until f2 = 0#
End With
End Sub

Note: The text is lost in translation. It actually reads "If f2 is not equal to 0# Then"

[aslakson]

Can't you just copy one of the datasets and paste it into the other one, and then sort on the time column (assuming the datasets are similarly formatted)? I do it fairly often . . .

al

[Harvard]

PM me, I will be happy to build you an App, no charge, but you will need to share the two data sources with me.