Compare and combine Date/Times between 2 sheets of data

Sparktracer · Sep 8, 2010

Hello experts,
This is what I want the finished work to look like (S3):

|-- A -- | -- B -- | -- C -- | --D --|-- E --|-- F --|--G --|-- -- H -- -- | -->
8/20/2010 21:18:03 -40 85.6 85 34
8/20/2010 21:19:03 -40 85.4 85 35
8/20/2010 21:20:03 -40 85 85 36 8/20/2010 21:20:43 Calculated Data 0.079 0.048 0.080 0.019 0.078 0.081
8/20/2010 21:21:03 -48.3 84.5 85 37
8/20/2010 21:22:03 -54.7 82.8 -40 xfer 0
8/20/2010 21:23:03 -39 85.5 -40 1
8/20/2010 21:24:03 -38.4 85.9 -40 2
8/20/2010 21:25:03 -39.2 85.5 -40 3
8/20/2010 21:26:03 -40.1 86.2 -40 4
8/20/2010 21:27:03 -40 85.3 -40 5
8/20/2010 21:28:03 -40.2 85.2 -40 6
8/20/2010 21:29:03 -40.2 84.8 -40 7
8/20/2010 21:30:03 -40.2 84.7 -40 8 8/20/2010 21:30:46 Calculated Data 0.051 0.064 0.056 (0.052) 0.023 0.082
8/20/2010 21:31:03 -40.1 84.9 -40 9
8/20/2010 21:32:03 -40.1 85.1 -40 10
8/20/2010 21:33:03 -40.1 85.2 -40 11
8/20/2010 21:34:03 -40.1 85.3 -40 12
8/20/2010 21:35:03 -40 85.5 -40 13
8/20/2010 21:36:03 -40.1 85.1 -40 14
8/20/2010 21:37:03 -40.1 84.9 -40 15
8/20/2010 21:38:03 -40.1 84.9 -40 16
8/20/2010 21:39:03 -40 85.3 -40 17
8/20/2010 21:40:03 -40.1 85.2 -40 18 8/20/2010 21:40:48 Calculated Data 0.055 0.038 0.051 0.028 0.044 0.047
8/20/2010 21:41:03 -40 84.9 -40 19
8/20/2010 21:42:03 -40.4 89 85 xfer 0

Here is the first set of data, (S1):

8/20/2010 21:18:03 -40 85.6 85
8/20/2010 21:19:03 -40 85.4 85
8/20/2010 21:20:03 -40 85 85
8/20/2010 21:21:03 -48.3 84.5 85
8/20/2010 21:22:03 -54.7 82.8 -40
8/20/2010 21:23:03 -39 85.5 -40
8/20/2010 21:24:03 -38.4 85.9 -40
8/20/2010 21:25:03 -39.2 85.5 -40
8/20/2010 21:26:03 -40.1 86.2 -40
8/20/2010 21:27:03 -40 85.3 -40
8/20/2010 21:28:03 -40.2 85.2 -40
8/20/2010 21:29:03 -40.2 84.8 -40
8/20/2010 21:30:03 -40.2 84.7 -40
8/20/2010 21:31:03 -40.1 84.9 -40
8/20/2010 21:32:03 -40.1 85.1 -40
8/20/2010 21:33:03 -40.1 85.2 -40
8/20/2010 21:34:03 -40.1 85.3 -40
8/20/2010 21:35:03 -40 85.5 -40
8/20/2010 21:36:03 -40.1 85.1 -40
8/20/2010 21:37:03 -40.1 84.9 -40
8/20/2010 21:38:03 -40.1 84.9 -40
8/20/2010 21:39:03 -40 85.3 -40
8/20/2010 21:40:03 -40.1 85.2 -40
8/20/2010 21:41:03 -40 84.9 -40
8/20/2010 21:42:03 -40.4 89 85

Here is the second set of data, (S2):

8/20/2010 21:20:43 Calculated Data 0.079 0.048 0.080 0.019 0.078 0.081
8/20/2010 21:30:46 Calculated Data 0.051 0.064 0.056 (0.052) 0.023 0.082
8/20/2010 21:40:48 Calculated Data 0.055 0.038 0.051 0.028 0.044 0.047
8/20/2010 21:50:48 Calculated Data 0.083 0.076 0.076 (0.012) 0.032 0.045
8/20/2010 22:00:49 Calculated Data 0.075 0.068 0.066 0.004 0.077 0.068
8/20/2010 22:10:50 Calculated Data (0.028) 0.014 0.068 (0.025) 0.042 0.049
8/20/2010 22:20:51 Calculated Data 0.056 0.041 0.053 0.020 0.040 0.049
8/20/2010 22:30:53 Calculated Data 0.110 0.065 0.044 0.068 0.129 0.086
8/20/2010 22:40:53 Calculated Data 0.047 0.005 0.088 0.026 0.089 0.063
8/20/2010 22:50:54 Calculated Data 0.122 0.013 0.043 0.090 (0.010) 0.038
8/20/2010 23:00:55 Calculated Data 0.034 0.043 0.045 0.021 0.033 0.049
8/20/2010 23:10:56 Calculated Data 0.062 (0.014) (0.001) (0.002) 0.073 0.035
8/20/2010 23:20:58 Calculated Data 0.048 0.135 0.090 0.011 0.027 0.040
8/20/2010 23:30:58 Calculated Data (0.064) 0.033 0.072 (0.045) (0.014) 0.010
8/20/2010 23:41:00 Calculated Data 0.055 0.044 0.055 0.031 0.051 0.050
8/20/2010 23:51:01 Calculated Data 0.037 0.115 0.447 (0.001) 0.004 0.075

Currently I am comparing the times in S1 column B with the times in S2 column A and then manually moving the data so the times matchup.

I have 3 months of data that I need to combine/compare.
A typical day of data:
S1 has 5 columns and up to 1445 rows.
S2 has 101 columns and up to 145 rows.

I have tried different formulas to move/copy the data from S2 into S1 but have had no luck.
Any assistance with this excel data compare and combine is greatly appreciated.

I am working with Windows XP and Office 2003.
We are allowed to use Macros and VB code. I have done very little with either.

Sparktracer

ps. I do not have HTMLMaker to create the formatted data. Sorry

Sparktracer · Sep 10, 2010

Zack Barresse said:
Ok. I'm heading out for a bit, may not get back to this today. If you can record a macro of you doing this and post the macro, that would help also.

Also, how do you want to fire this off? Open a workbook, have a button there? On your command bar as a menu?

Wonderful Zack. I have days and days of data to manually compare anyway.

As for execution, a button on the toolbar that will do the comparing and S3 creation. The file names that I am importing with the data are constantly changing, so manually importing the 2 data files is ok too.

I will attempt to create a macro (or 2) to show you how I am doing this now. Will post it later.

Sparktracer · Sep 10, 2010

Here is a macro to import my 2 files for 9/9/2010.

I also add my formulas and conditional formatting.

Not sure how to add this as a copy from the macro editor. Copied the macro into WS to maintain formatting.

Excel Workbook

A

1

Sub Import_Data()

2

'

3

' Import_Data Macro

4

' Macro recorded by Storage Desk

5

'

6

' Keyboard Shortcut: Ctrl+Shift+D

7

'

8

With ActiveSheet.QueryTables.Add(Connection:= _

9

"TEXT;G:\Lab_Data\Tests\34711\tr14061\ts-dc raw data\TR14061 chamber 9-9-2010.csv" _

10

, Destination:=Range("A1"))

11

.Name = "TR14061 chamber 9-9-2010"

12

.FieldNames = True

13

.RowNumbers = False

14

.FillAdjacentFormulas = False

15

.PreserveFormatting = True

16

.RefreshOnFileOpen = False

17

.RefreshStyle = xlInsertDeleteCells

18

.SavePassword = False

19

.SaveData = True

20

.AdjustColumnWidth = True

21

.RefreshPeriod = 0

22

.TextFilePromptOnRefresh = False

23

.TextFilePlatform = 437

24

.TextFileStartRow = 1

25

.TextFileParseType = xlDelimited

26

.TextFileTextQualifier = xlTextQualifierDoubleQuote

27

.TextFileConsecutiveDelimiter = False

28

.TextFileTabDelimiter = False

29

.TextFileSemicolonDelimiter = False

30

.TextFileCommaDelimiter = True

31

.TextFileSpaceDelimiter = False

32

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)

33

.TextFileTrailingMinusNumbers = True

34

.Refresh BackgroundQuery:=False

35

End With

36

Sheets.Add

37

With ActiveSheet.QueryTables.Add(Connection:= _

38

"TEXT;G:\Lab_Data\Tests\34711\tr14061\ts-dc raw data\TR14061 TS DC 3. 22A", _

39

Destination:=Range("A1"))

40

.Name = "TR14061 TS DC 3"

41

.FieldNames = True

42

.RowNumbers = False

43

.FillAdjacentFormulas = False

44

.PreserveFormatting = True

45

.RefreshOnFileOpen = False

46

.RefreshStyle = xlInsertDeleteCells

47

.SavePassword = False

48

.SaveData = True

49

.AdjustColumnWidth = True

50

.RefreshPeriod = 0

51

.TextFilePromptOnRefresh = False

52

.TextFilePlatform = 437

53

.TextFileStartRow = 1

54

.TextFileParseType = xlDelimited

55

.TextFileTextQualifier = xlTextQualifierDoubleQuote

56

.TextFileConsecutiveDelimiter = False

57

.TextFileTabDelimiter = True

58

.TextFileSemicolonDelimiter = False

59

.TextFileCommaDelimiter = False

60

.TextFileSpaceDelimiter = False

61

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _

62

1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _

63

, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _

64

1)

65

.TextFileTrailingMinusNumbers = True

66

.Refresh BackgroundQuery:=False

67

End With

68

Columns("A:A").Select

69

Selection.NumberFormat = "m/d/yyyy h:mm:ss"

70

Columns("C:CT").Select

71

Selection.NumberFormat = "0.000_);[Red](0.000)"

72

Sheets("Sheet1").Select

73

Range("F5").Select

74

ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],"""",""xfer"")"

75

Range("F5").Select

76

Selection.AutoFill Destination:=Range("F5:F1454"), Type:=xlFillDefault

77

Range("F5:F1454").Select

78

ActiveWindow.ScrollRow = 1418

79

ActiveWindow.ScrollRow = 1416

80

ActiveWindow.ScrollRow = 1414

81

ActiveWindow.ScrollRow = 1412

82

ActiveWindow.ScrollRow = 1408

83

ActiveWindow.ScrollRow = 1404

84

ActiveWindow.ScrollRow = 1396

85

ActiveWindow.ScrollRow = 1385

86

ActiveWindow.ScrollRow = 1373

87

ActiveWindow.ScrollRow = 1357

88

ActiveWindow.ScrollRow = 1337

89

ActiveWindow.ScrollRow = 1316

90

ActiveWindow.ScrollRow = 1292

91

ActiveWindow.ScrollRow = 1267

92

ActiveWindow.ScrollRow = 1243

93

ActiveWindow.ScrollRow = 1218

94

ActiveWindow.ScrollRow = 1190

95

ActiveWindow.ScrollRow = 1164

96

ActiveWindow.ScrollRow = 1139

97

ActiveWindow.ScrollRow = 1113

98

ActiveWindow.ScrollRow = 1086

99

ActiveWindow.ScrollRow = 1060

100

ActiveWindow.ScrollRow = 1035

101

ActiveWindow.ScrollRow = 1003

102

ActiveWindow.ScrollRow = 974

103

ActiveWindow.ScrollRow = 942

104

ActiveWindow.ScrollRow = 903

105

ActiveWindow.ScrollRow = 874

106

ActiveWindow.ScrollRow = 842

107

ActiveWindow.ScrollRow = 807

108

ActiveWindow.ScrollRow = 773

109

ActiveWindow.ScrollRow = 734

110

ActiveWindow.ScrollRow = 707

111

ActiveWindow.ScrollRow = 679

112

ActiveWindow.ScrollRow = 655

113

ActiveWindow.ScrollRow = 628

114

ActiveWindow.ScrollRow = 602

115

ActiveWindow.ScrollRow = 579

116

ActiveWindow.ScrollRow = 555

117

ActiveWindow.ScrollRow = 530

118

ActiveWindow.ScrollRow = 510

119

ActiveWindow.ScrollRow = 488

120

ActiveWindow.ScrollRow = 471

121

ActiveWindow.ScrollRow = 449

122

ActiveWindow.ScrollRow = 433

123

ActiveWindow.ScrollRow = 418

124

ActiveWindow.ScrollRow = 406

125

ActiveWindow.ScrollRow = 394

126

ActiveWindow.ScrollRow = 382

127

ActiveWindow.ScrollRow = 374

128

ActiveWindow.ScrollRow = 361

129

ActiveWindow.ScrollRow = 353

130

ActiveWindow.ScrollRow = 341

131

ActiveWindow.ScrollRow = 333

132

ActiveWindow.ScrollRow = 325

133

ActiveWindow.ScrollRow = 317

134

ActiveWindow.ScrollRow = 310

135

ActiveWindow.ScrollRow = 298

136

ActiveWindow.ScrollRow = 286

137

ActiveWindow.ScrollRow = 276

138

ActiveWindow.ScrollRow = 262

139

ActiveWindow.ScrollRow = 251

140

ActiveWindow.ScrollRow = 237

141

ActiveWindow.ScrollRow = 221

142

ActiveWindow.ScrollRow = 207

143

ActiveWindow.ScrollRow = 194

144

ActiveWindow.ScrollRow = 178

145

ActiveWindow.ScrollRow = 164

146

ActiveWindow.ScrollRow = 150

147

ActiveWindow.ScrollRow = 137

148

ActiveWindow.ScrollRow = 125

149

ActiveWindow.ScrollRow = 113

150

ActiveWindow.ScrollRow = 105

151

ActiveWindow.ScrollRow = 97

152

ActiveWindow.ScrollRow = 89

153

ActiveWindow.ScrollRow = 84

154

ActiveWindow.ScrollRow = 82

155

ActiveWindow.ScrollRow = 78

156

ActiveWindow.ScrollRow = 76

157

ActiveWindow.ScrollRow = 74

158

ActiveWindow.ScrollRow = 72

159

ActiveWindow.ScrollRow = 70

160

ActiveWindow.ScrollRow = 68

161

ActiveWindow.ScrollRow = 66

162

ActiveWindow.ScrollRow = 64

163

ActiveWindow.ScrollRow = 60

164

ActiveWindow.ScrollRow = 54

165

ActiveWindow.ScrollRow = 50

166

ActiveWindow.ScrollRow = 44

167

ActiveWindow.ScrollRow = 38

168

ActiveWindow.ScrollRow = 32

169

ActiveWindow.ScrollRow = 27

170

ActiveWindow.ScrollRow = 17

171

ActiveWindow.ScrollRow = 11

172

ActiveWindow.ScrollRow = 5

173

ActiveWindow.ScrollRow = 1

174

Selection.FormatConditions.Delete

175

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

176

Formula1:="=""xfer"""

177

Selection.FormatConditions(1).Interior.ColorIndex = 6

178

Range("G4").Select

179

ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""xfer"",0,R[-1]C+1)"

180

Range("G4").Select

181

Selection.AutoFill Destination:=Range("G4:G1454"), Type:=xlFillDefault

182

Range("G4:G1454").Select

183

ActiveWindow.ScrollRow = 1413

184

ActiveWindow.ScrollRow = 1411

185

ActiveWindow.ScrollRow = 1409

186

ActiveWindow.ScrollRow = 1405

187

ActiveWindow.ScrollRow = 1403

188

ActiveWindow.ScrollRow = 1399

189

ActiveWindow.ScrollRow = 1395

190

ActiveWindow.ScrollRow = 1389

191

ActiveWindow.ScrollRow = 1382

192

ActiveWindow.ScrollRow = 1374

193

ActiveWindow.ScrollRow = 1366

194

ActiveWindow.ScrollRow = 1354

195

ActiveWindow.ScrollRow = 1344

196

ActiveWindow.ScrollRow = 1331

197

ActiveWindow.ScrollRow = 1317

198

ActiveWindow.ScrollRow = 1307

199

ActiveWindow.ScrollRow = 1292

200

ActiveWindow.ScrollRow = 1274

201

ActiveWindow.ScrollRow = 1258

202

ActiveWindow.ScrollRow = 1241

203

ActiveWindow.ScrollRow = 1221

204

ActiveWindow.ScrollRow = 1201

205

ActiveWindow.ScrollRow = 1182

206

ActiveWindow.ScrollRow = 1164

207

ActiveWindow.ScrollRow = 1145

208

ActiveWindow.ScrollRow = 1121

209

ActiveWindow.ScrollRow = 1102

210

ActiveWindow.ScrollRow = 1078

211

ActiveWindow.ScrollRow = 1055

212

ActiveWindow.ScrollRow = 1031

213

ActiveWindow.ScrollRow = 1008

214

ActiveWindow.ScrollRow = 982

215

ActiveWindow.ScrollRow = 959

216

ActiveWindow.ScrollRow = 935

217

ActiveWindow.ScrollRow = 908

218

ActiveWindow.ScrollRow = 884

219

ActiveWindow.ScrollRow = 861

220

ActiveWindow.ScrollRow = 837

221

ActiveWindow.ScrollRow = 814

222

ActiveWindow.ScrollRow = 790

223

ActiveWindow.ScrollRow = 767

224

ActiveWindow.ScrollRow = 743

225

ActiveWindow.ScrollRow = 720

226

ActiveWindow.ScrollRow = 696

227

ActiveWindow.ScrollRow = 673

228

ActiveWindow.ScrollRow = 653

229

ActiveWindow.ScrollRow = 632

230

ActiveWindow.ScrollRow = 614

231

ActiveWindow.ScrollRow = 596

232

ActiveWindow.ScrollRow = 577

233

ActiveWindow.ScrollRow = 561

234

ActiveWindow.ScrollRow = 545

235

ActiveWindow.ScrollRow = 532

236

ActiveWindow.ScrollRow = 518

237

ActiveWindow.ScrollRow = 506

238

ActiveWindow.ScrollRow = 496

239

ActiveWindow.ScrollRow = 485

240

ActiveWindow.ScrollRow = 475

241

ActiveWindow.ScrollRow = 463

242

ActiveWindow.ScrollRow = 453

243

ActiveWindow.ScrollRow = 446

244

ActiveWindow.ScrollRow = 434

245

ActiveWindow.ScrollRow = 424

246

ActiveWindow.ScrollRow = 416

247

ActiveWindow.ScrollRow = 406

248

ActiveWindow.ScrollRow = 397

249

ActiveWindow.ScrollRow = 389

250

ActiveWindow.ScrollRow = 377

251

ActiveWindow.ScrollRow = 367

252

ActiveWindow.ScrollRow = 355

253

ActiveWindow.ScrollRow = 346

254

ActiveWindow.ScrollRow = 336

255

ActiveWindow.ScrollRow = 324

256

ActiveWindow.ScrollRow = 310

257

ActiveWindow.ScrollRow = 301

258

ActiveWindow.ScrollRow = 287

259

ActiveWindow.ScrollRow = 275

260

ActiveWindow.ScrollRow = 263

261

ActiveWindow.ScrollRow = 250

262

ActiveWindow.ScrollRow = 240

263

ActiveWindow.ScrollRow = 230

264

ActiveWindow.ScrollRow = 218

265

ActiveWindow.ScrollRow = 211

266

ActiveWindow.ScrollRow = 203

267

ActiveWindow.ScrollRow = 195

268

ActiveWindow.ScrollRow = 189

269

ActiveWindow.ScrollRow = 183

270

ActiveWindow.ScrollRow = 177

271

ActiveWindow.ScrollRow = 173

272

ActiveWindow.ScrollRow = 169

273

ActiveWindow.ScrollRow = 167

274

ActiveWindow.ScrollRow = 166

275

ActiveWindow.ScrollRow = 162

276

ActiveWindow.ScrollRow = 160

277

ActiveWindow.ScrollRow = 158

278

ActiveWindow.ScrollRow = 156

279

ActiveWindow.ScrollRow = 154

280

ActiveWindow.ScrollRow = 152

281

ActiveWindow.ScrollRow = 150

282

ActiveWindow.ScrollRow = 148

283

ActiveWindow.ScrollRow = 146

284

ActiveWindow.ScrollRow = 142

285

ActiveWindow.ScrollRow = 138

286

ActiveWindow.ScrollRow = 136

287

ActiveWindow.ScrollRow = 132

288

ActiveWindow.ScrollRow = 128

289

ActiveWindow.ScrollRow = 124

290

ActiveWindow.ScrollRow = 120

291

ActiveWindow.ScrollRow = 117

292

ActiveWindow.ScrollRow = 113

293

ActiveWindow.ScrollRow = 109

294

ActiveWindow.ScrollRow = 105

295

ActiveWindow.ScrollRow = 101

296

ActiveWindow.ScrollRow = 97

297

ActiveWindow.ScrollRow = 91

298

ActiveWindow.ScrollRow = 89

299

ActiveWindow.ScrollRow = 85

300

ActiveWindow.ScrollRow = 81

301

ActiveWindow.ScrollRow = 79

302

ActiveWindow.ScrollRow = 77

303

ActiveWindow.ScrollRow = 73

304

ActiveWindow.ScrollRow = 70

305

ActiveWindow.ScrollRow = 68

306

ActiveWindow.ScrollRow = 66

307

ActiveWindow.ScrollRow = 62

308

ActiveWindow.ScrollRow = 60

309

ActiveWindow.ScrollRow = 58

310

ActiveWindow.ScrollRow = 54

311

ActiveWindow.ScrollRow = 52

312

ActiveWindow.ScrollRow = 50

313

ActiveWindow.ScrollRow = 46

314

ActiveWindow.ScrollRow = 44

315

ActiveWindow.ScrollRow = 42

316

ActiveWindow.ScrollRow = 40

317

ActiveWindow.ScrollRow = 38

318

ActiveWindow.ScrollRow = 36

319

ActiveWindow.ScrollRow = 32

320

ActiveWindow.ScrollRow = 30

321

ActiveWindow.ScrollRow = 26

322

ActiveWindow.ScrollRow = 25

323

ActiveWindow.ScrollRow = 23

324

ActiveWindow.ScrollRow = 19

325

ActiveWindow.ScrollRow = 17

326

ActiveWindow.ScrollRow = 15

327

ActiveWindow.ScrollRow = 13

328

ActiveWindow.ScrollRow = 11

329

ActiveWindow.ScrollRow = 9

330

ActiveWindow.ScrollRow = 7

331

ActiveWindow.ScrollRow = 5

332

ActiveWindow.ScrollRow = 1

333

Selection.FormatConditions.Delete

334

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _

335

Formula1:="8", Formula2:="18"

336

Selection.FormatConditions(1).Interior.ColorIndex = 8

337

Range("G4").Select

338

ActiveCell.FormulaR1C1 = "8"

339

Range("G5").Select

340

End Sub

Sheet3

Sparktracer · Sep 10, 2010

Ok. I have the VBMaker xla running now on this computer. I hope this helps you.

Sub Import_Data() ' ' Import_Data Macro ' Macro recorded**by Storage Desk ' ' Keyboard Shortcut: Ctrl+Shift+D ' ****With ActiveSheet.QueryTables.Add(Connection:= _ ********"TEXT;G:\Lab_Data\Tests\34711\tr14061\ts-dc raw data\TR14061 chamber 9-9-2010.csv" _ ********, Destination:=Range("A1")) ********.Name = "TR14061 chamber 9-9-2010" ********.FieldNames = True ********.RowNumbers = False ********.FillAdjacentFormulas = False ********.PreserveFormatting = True ********.RefreshOnFileOpen = False ********.RefreshStyle = xlInsertDeleteCells ********.SavePassword = False ********.SaveData = True ********.AdjustColumnWidth = True ********.RefreshPeriod = 0 ********.TextFilePromptOnRefresh = False ********.TextFilePlatform = 437 ********.TextFileStartRow = 1 ********.TextFileParseType = xlDelimited ********.TextFileTextQualifier = xlTextQualifierDoubleQuote ********.TextFileConsecutiveDelimiter = False ********.TextFileTabDelimiter = False ********.TextFileSemicolonDelimiter = False ********.TextFileCommaDelimiter = True ********.TextFileSpaceDelimiter = False ********.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1) ********.TextFileTrailingMinusNumbers = True ********.Refresh BackgroundQuery:=False ****End With ****Sheets.Add ****With ActiveSheet.QueryTables.Add(Connection:= _ ********"TEXT;G:\Lab_Data\Tests\34711\tr14061\ts-dc raw data\TR14061 TS DC 3. 22A", _ ********Destination:=Range("A1")) ********.Name = "TR14061 TS DC 3" ********.FieldNames = True ********.RowNumbers = False ********.FillAdjacentFormulas = False ********.PreserveFormatting = True ********.RefreshOnFileOpen = False ********.RefreshStyle = xlInsertDeleteCells ********.SavePassword = False ********.SaveData = True ********.AdjustColumnWidth = True ********.RefreshPeriod = 0 ********.TextFilePromptOnRefresh = False ********.TextFilePlatform = 437 ********.TextFileStartRow = 1 ********.TextFileParseType = xlDelimited ********.TextFileTextQualifier = xlTextQualifierDoubleQuote ********.TextFileConsecutiveDelimiter = False ********.TextFileTabDelimiter = True ********.TextFileSemicolonDelimiter = False ********.TextFileCommaDelimiter = False ********.TextFileSpaceDelimiter = False ********.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ ********1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ ********, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ ********1) ********.TextFileTrailingMinusNumbers = True ********.Refresh BackgroundQuery:=False ****End With ****Columns("A:A").Select ****Selection.NumberFormat = "m/d/yyyy h:mm:ss" ****Columns("C:CT").Select ****Selection.NumberFormat = "0.000_);[Red](0.000)" ****Sheets("Sheet1").Select ****Range("F5").Select ****ActiveCell.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],"""",""xfer"")" ****Range("F5").Select ****Selection.AutoFill Destination:=Range("F5:F1454"), Type:=xlFillDefault ****Range("F5:F1454").Select ****ActiveWindow.ScrollRow = 1418 ****ActiveWindow.ScrollRow = 1416 ****ActiveWindow.ScrollRow = 1414 ****ActiveWindow.ScrollRow = 1412 ****ActiveWindow.ScrollRow = 1408 ****ActiveWindow.ScrollRow = 1404 ****ActiveWindow.ScrollRow = 1396 ****ActiveWindow.ScrollRow = 1385 ****ActiveWindow.ScrollRow = 1373 ****ActiveWindow.ScrollRow = 1357 ****ActiveWindow.ScrollRow = 1337 ****ActiveWindow.ScrollRow = 1316 ****ActiveWindow.ScrollRow = 1292 ****ActiveWindow.ScrollRow = 1267 ****ActiveWindow.ScrollRow = 1243 ****ActiveWindow.ScrollRow = 1218 ****ActiveWindow.ScrollRow = 1190 ****ActiveWindow.ScrollRow = 1164 ****ActiveWindow.ScrollRow = 1139 ****ActiveWindow.ScrollRow = 1113 ****ActiveWindow.ScrollRow = 1086 ****ActiveWindow.ScrollRow = 1060 ****ActiveWindow.ScrollRow = 1035 ****ActiveWindow.ScrollRow = 1003 ****ActiveWindow.ScrollRow = 974 ****ActiveWindow.ScrollRow = 942 ****ActiveWindow.ScrollRow = 903 ****ActiveWindow.ScrollRow = 874 ****ActiveWindow.ScrollRow = 842 ****ActiveWindow.ScrollRow = 807 ****ActiveWindow.ScrollRow = 773 ****ActiveWindow.ScrollRow = 734 ****ActiveWindow.ScrollRow = 707 ****ActiveWindow.ScrollRow = 679 ****ActiveWindow.ScrollRow = 655 ****ActiveWindow.ScrollRow = 628 ****ActiveWindow.ScrollRow = 602 ****ActiveWindow.ScrollRow = 579 ****ActiveWindow.ScrollRow = 555 ****ActiveWindow.ScrollRow = 530 ****ActiveWindow.ScrollRow = 510 ****ActiveWindow.ScrollRow = 488 ****ActiveWindow.ScrollRow = 471 ****ActiveWindow.ScrollRow = 449 ****ActiveWindow.ScrollRow = 433 ****ActiveWindow.ScrollRow = 418 ****ActiveWindow.ScrollRow = 406 ****ActiveWindow.ScrollRow = 394 ****ActiveWindow.ScrollRow = 382 ****ActiveWindow.ScrollRow = 374 ****ActiveWindow.ScrollRow = 361 ****ActiveWindow.ScrollRow = 353 ****ActiveWindow.ScrollRow = 341 ****ActiveWindow.ScrollRow = 333 ****ActiveWindow.ScrollRow = 325 ****ActiveWindow.ScrollRow = 317 ****ActiveWindow.ScrollRow = 310 ****ActiveWindow.ScrollRow = 298 ****ActiveWindow.ScrollRow = 286 ****ActiveWindow.ScrollRow = 276 ****ActiveWindow.ScrollRow = 262 ****ActiveWindow.ScrollRow = 251 ****ActiveWindow.ScrollRow = 237 ****ActiveWindow.ScrollRow = 221 ****ActiveWindow.ScrollRow = 207 ****ActiveWindow.ScrollRow = 194 ****ActiveWindow.ScrollRow = 178 ****ActiveWindow.ScrollRow = 164 ****ActiveWindow.ScrollRow = 150 ****ActiveWindow.ScrollRow = 137 ****ActiveWindow.ScrollRow = 125 ****ActiveWindow.ScrollRow = 113 ****ActiveWindow.ScrollRow = 105 ****ActiveWindow.ScrollRow = 97 ****ActiveWindow.ScrollRow = 89 ****ActiveWindow.ScrollRow = 84 ****ActiveWindow.ScrollRow = 82 ****ActiveWindow.ScrollRow = 78 ****ActiveWindow.ScrollRow = 76 ****ActiveWindow.ScrollRow = 74 ****ActiveWindow.ScrollRow = 72 ****ActiveWindow.ScrollRow = 70 ****ActiveWindow.ScrollRow = 68 ****ActiveWindow.ScrollRow = 66 ****ActiveWindow.ScrollRow = 64 ****ActiveWindow.ScrollRow = 60 ****ActiveWindow.ScrollRow = 54 ****ActiveWindow.ScrollRow = 50 ****ActiveWindow.ScrollRow = 44 ****ActiveWindow.ScrollRow = 38 ****ActiveWindow.ScrollRow = 32 ****ActiveWindow.ScrollRow = 27 ****ActiveWindow.ScrollRow = 17 ****ActiveWindow.ScrollRow = 11 ****ActiveWindow.ScrollRow = 5 ****ActiveWindow.ScrollRow = 1 ****Selection.FormatConditions.Delete ****Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ ********Formula1:="=""xfer""" ****Selection.FormatConditions(1).Interior.ColorIndex = 6 ****Range("G4").Select ****ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""xfer"",0,R[-1]C+1)" ****Range("G4").Select ****Selection.AutoFill Destination:=Range("G4:G1454"), Type:=xlFillDefault ****Range("G4:G1454").Select ****ActiveWindow.ScrollRow = 1413 ****ActiveWindow.ScrollRow = 1411 ****ActiveWindow.ScrollRow = 1409 ****ActiveWindow.ScrollRow = 1405 ****ActiveWindow.ScrollRow = 1403 ****ActiveWindow.ScrollRow = 1399 ****ActiveWindow.ScrollRow = 1395 ****ActiveWindow.ScrollRow = 1389 ****ActiveWindow.ScrollRow = 1382 ****ActiveWindow.ScrollRow = 1374 ****ActiveWindow.ScrollRow = 1366 ****ActiveWindow.ScrollRow = 1354 ****ActiveWindow.ScrollRow = 1344 ****ActiveWindow.ScrollRow = 1331 ****ActiveWindow.ScrollRow = 1317 ****ActiveWindow.ScrollRow = 1307 ****ActiveWindow.ScrollRow = 1292 ****ActiveWindow.ScrollRow = 1274 ****ActiveWindow.ScrollRow = 1258 ****ActiveWindow.ScrollRow = 1241 ****ActiveWindow.ScrollRow = 1221 ****ActiveWindow.ScrollRow = 1201 ****ActiveWindow.ScrollRow = 1182 ****ActiveWindow.ScrollRow = 1164 ****ActiveWindow.ScrollRow = 1145 ****ActiveWindow.ScrollRow = 1121 ****ActiveWindow.ScrollRow = 1102 ****ActiveWindow.ScrollRow = 1078 ****ActiveWindow.ScrollRow = 1055 ****ActiveWindow.ScrollRow = 1031 ****ActiveWindow.ScrollRow = 1008 ****ActiveWindow.ScrollRow = 982 ****ActiveWindow.ScrollRow = 959 ****ActiveWindow.ScrollRow = 935 ****ActiveWindow.ScrollRow = 908 ****ActiveWindow.ScrollRow = 884 ****ActiveWindow.ScrollRow = 861 ****ActiveWindow.ScrollRow = 837 ****ActiveWindow.ScrollRow = 814 ****ActiveWindow.ScrollRow = 790 ****ActiveWindow.ScrollRow = 767 ****ActiveWindow.ScrollRow = 743 ****ActiveWindow.ScrollRow = 720 ****ActiveWindow.ScrollRow = 696 ****ActiveWindow.ScrollRow = 673 ****ActiveWindow.ScrollRow = 653 ****ActiveWindow.ScrollRow = 632 ****ActiveWindow.ScrollRow = 614 ****ActiveWindow.ScrollRow = 596 ****ActiveWindow.ScrollRow = 577 ****ActiveWindow.ScrollRow = 561 ****ActiveWindow.ScrollRow = 545 ****ActiveWindow.ScrollRow = 532 ****ActiveWindow.ScrollRow = 518 ****ActiveWindow.ScrollRow = 506 ****ActiveWindow.ScrollRow = 496 ****ActiveWindow.ScrollRow = 485 ****ActiveWindow.ScrollRow = 475 ****ActiveWindow.ScrollRow = 463 ****ActiveWindow.ScrollRow = 453 ****ActiveWindow.ScrollRow = 446 ****ActiveWindow.ScrollRow = 434 ****ActiveWindow.ScrollRow = 424 ****ActiveWindow.ScrollRow = 416 ****ActiveWindow.ScrollRow = 406 ****ActiveWindow.ScrollRow = 397 ****ActiveWindow.ScrollRow = 389 ****ActiveWindow.ScrollRow = 377 ****ActiveWindow.ScrollRow = 367 ****ActiveWindow.ScrollRow = 355 ****ActiveWindow.ScrollRow = 346 ****ActiveWindow.ScrollRow = 336 ****ActiveWindow.ScrollRow = 324 ****ActiveWindow.ScrollRow = 310 ****ActiveWindow.ScrollRow = 301 ****ActiveWindow.ScrollRow = 287 ****ActiveWindow.ScrollRow = 275 ****ActiveWindow.ScrollRow = 263 ****ActiveWindow.ScrollRow = 250 ****ActiveWindow.ScrollRow = 240 ****ActiveWindow.ScrollRow = 230 ****ActiveWindow.ScrollRow = 218 ****ActiveWindow.ScrollRow = 211 ****ActiveWindow.ScrollRow = 203 ****ActiveWindow.ScrollRow = 195 ****ActiveWindow.ScrollRow = 189 ****ActiveWindow.ScrollRow = 183 ****ActiveWindow.ScrollRow = 177 ****ActiveWindow.ScrollRow = 173 ****ActiveWindow.ScrollRow = 169 ****ActiveWindow.ScrollRow = 167 ****ActiveWindow.ScrollRow = 166 ****ActiveWindow.ScrollRow = 162 ****ActiveWindow.ScrollRow = 160 ****ActiveWindow.ScrollRow = 158 ****ActiveWindow.ScrollRow = 156 ****ActiveWindow.ScrollRow = 154 ****ActiveWindow.ScrollRow = 152 ****ActiveWindow.ScrollRow = 150 ****ActiveWindow.ScrollRow = 148 ****ActiveWindow.ScrollRow = 146 ****ActiveWindow.ScrollRow = 142 ****ActiveWindow.ScrollRow = 138 ****ActiveWindow.ScrollRow = 136 ****ActiveWindow.ScrollRow = 132 ****ActiveWindow.ScrollRow = 128 ****ActiveWindow.ScrollRow = 124 ****ActiveWindow.ScrollRow = 120 ****ActiveWindow.ScrollRow = 117 ****ActiveWindow.ScrollRow = 113 ****ActiveWindow.ScrollRow = 109 ****ActiveWindow.ScrollRow = 105 ****ActiveWindow.ScrollRow = 101 ****ActiveWindow.ScrollRow = 97 ****ActiveWindow.ScrollRow = 91 ****ActiveWindow.ScrollRow = 89 ****ActiveWindow.ScrollRow = 85 ****ActiveWindow.ScrollRow = 81 ****ActiveWindow.ScrollRow = 79 ****ActiveWindow.ScrollRow = 77 ****ActiveWindow.ScrollRow = 73 ****ActiveWindow.ScrollRow = 70 ****ActiveWindow.ScrollRow = 68 ****ActiveWindow.ScrollRow = 66 ****ActiveWindow.ScrollRow = 62 ****ActiveWindow.ScrollRow = 60 ****ActiveWindow.ScrollRow = 58 ****ActiveWindow.ScrollRow = 54 ****ActiveWindow.ScrollRow = 52 ****ActiveWindow.ScrollRow = 50 ****ActiveWindow.ScrollRow = 46 ****ActiveWindow.ScrollRow = 44 ****ActiveWindow.ScrollRow = 42 ****ActiveWindow.ScrollRow = 40 ****ActiveWindow.ScrollRow = 38 ****ActiveWindow.ScrollRow = 36 ****ActiveWindow.ScrollRow = 32 ****ActiveWindow.ScrollRow = 30 ****ActiveWindow.ScrollRow = 26 ****ActiveWindow.ScrollRow = 25 ****ActiveWindow.ScrollRow = 23 ****ActiveWindow.ScrollRow = 19 ****ActiveWindow.ScrollRow = 17 ****ActiveWindow.ScrollRow = 15 ****ActiveWindow.ScrollRow = 13 ****ActiveWindow.ScrollRow = 11 ****ActiveWindow.ScrollRow = 9 ****ActiveWindow.ScrollRow = 7 ****ActiveWindow.ScrollRow = 5 ****ActiveWindow.ScrollRow = 1 ****Selection.FormatConditions.Delete ****Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ ********Formula1:="8", Formula2:="18" ****Selection.FormatConditions(1).Interior.ColorIndex = 8 ****Range("G4").Select ****ActiveCell.FormulaR1C1 = "8" ****Range("G5").Select End Sub

Sparktracer · Sep 10, 2010

Here is a portion of my manual compre. All I am doing is looking at the last S2 entry and moving the entire column of data in S1 col H to match times in S1 col B. Once the bottom cell is aligned correctly, I select all column H data except the last cell and then align the bottom selected cell to its equal in column B.

Rich (BB code):

Sub compare()
'
' compare Macro
' Macro recorded 9/10/2010 by Storage Desk
'
' Keyboard Shortcut: Ctrl+Shift+C
'
    Columns("H:H").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy h:mm:ss"
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 61
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 76
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 107
    ActiveWindow.ScrollRow = 113
    ActiveWindow.ScrollRow = 117
    ActiveWindow.ScrollRow = 122
    ActiveWindow.ScrollRow = 126
    ActiveWindow.ScrollRow = 133
    ActiveWindow.ScrollRow = 137
    ActiveWindow.ScrollRow = 141
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 154
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 165
    ActiveWindow.ScrollRow = 171
    ActiveWindow.ScrollRow = 176
    ActiveWindow.ScrollRow = 185
    ActiveWindow.ScrollRow = 193
    ActiveWindow.ScrollRow = 200
    ActiveWindow.ScrollRow = 210
    ActiveWindow.ScrollRow = 217
    ActiveWindow.ScrollRow = 225
    ActiveWindow.ScrollRow = 230
    ActiveWindow.ScrollRow = 241
    ActiveWindow.ScrollRow = 249
    ActiveWindow.ScrollRow = 256
    ActiveWindow.ScrollRow = 266
    ActiveWindow.ScrollRow = 275
    ActiveWindow.ScrollRow = 284
    ActiveWindow.ScrollRow = 294
    ActiveWindow.ScrollRow = 301
    ActiveWindow.ScrollRow = 312
    ActiveWindow.ScrollRow = 323
    ActiveWindow.ScrollRow = 333
    ActiveWindow.ScrollRow = 346
    ActiveWindow.ScrollRow = 357
    ActiveWindow.ScrollRow = 368
    ActiveWindow.ScrollRow = 379
    ActiveWindow.ScrollRow = 392
    ActiveWindow.ScrollRow = 402
    ActiveWindow.ScrollRow = 413
    ActiveWindow.ScrollRow = 424
    ActiveWindow.ScrollRow = 433
    ActiveWindow.ScrollRow = 445
    ActiveWindow.ScrollRow = 452
    ActiveWindow.ScrollRow = 463
    ActiveWindow.ScrollRow = 471
    ActiveWindow.ScrollRow = 480
    ActiveWindow.ScrollRow = 487
    ActiveWindow.ScrollRow = 495
    ActiveWindow.ScrollRow = 504
    ActiveWindow.ScrollRow = 512
    ActiveWindow.ScrollRow = 519
    ActiveWindow.ScrollRow = 527
    ActiveWindow.ScrollRow = 534
    ActiveWindow.ScrollRow = 545
    ActiveWindow.ScrollRow = 553
    ActiveWindow.ScrollRow = 560
    ActiveWindow.ScrollRow = 571
    ActiveWindow.ScrollRow = 581
    ActiveWindow.ScrollRow = 594
    ActiveWindow.ScrollRow = 603
    ActiveWindow.ScrollRow = 614
    ActiveWindow.ScrollRow = 623
    ActiveWindow.ScrollRow = 635
    ActiveWindow.ScrollRow = 644
    ActiveWindow.ScrollRow = 651
    ActiveWindow.ScrollRow = 663
    ActiveWindow.ScrollRow = 670
    ActiveWindow.ScrollRow = 679
    ActiveWindow.ScrollRow = 689
    ActiveWindow.ScrollRow = 696
    ActiveWindow.ScrollRow = 704
    ActiveWindow.ScrollRow = 711
    ActiveWindow.ScrollRow = 718
    ActiveWindow.ScrollRow = 726
    ActiveWindow.ScrollRow = 733
    ActiveWindow.ScrollRow = 739
    ActiveWindow.ScrollRow = 745
    ActiveWindow.ScrollRow = 752
    ActiveWindow.ScrollRow = 758
    ActiveWindow.ScrollRow = 763
    ActiveWindow.ScrollRow = 769
    ActiveWindow.ScrollRow = 773
    ActiveWindow.ScrollRow = 778
    ActiveWindow.ScrollRow = 782
    ActiveWindow.ScrollRow = 787
    ActiveWindow.ScrollRow = 793
    ActiveWindow.ScrollRow = 799
    ActiveWindow.ScrollRow = 804
    ActiveWindow.ScrollRow = 810
    ActiveWindow.ScrollRow = 815
    ActiveWindow.ScrollRow = 821
    ActiveWindow.ScrollRow = 827
    ActiveWindow.ScrollRow = 834
    ActiveWindow.ScrollRow = 841
    ActiveWindow.ScrollRow = 847
    ActiveWindow.ScrollRow = 856
    ActiveWindow.ScrollRow = 860
    ActiveWindow.ScrollRow = 869
    ActiveWindow.ScrollRow = 875
    ActiveWindow.ScrollRow = 881
    ActiveWindow.ScrollRow = 884
    ActiveWindow.ScrollRow = 888
    ActiveWindow.ScrollRow = 890
    ActiveWindow.ScrollRow = 894
    ActiveWindow.ScrollRow = 896
    ActiveWindow.ScrollRow = 897
    ActiveWindow.ScrollRow = 899
    ActiveWindow.ScrollRow = 901
    ActiveWindow.ScrollRow = 903
    ActiveWindow.ScrollRow = 905
    ActiveWindow.ScrollRow = 907
    ActiveWindow.ScrollRow = 912
    ActiveWindow.ScrollRow = 914
    ActiveWindow.ScrollRow = 918
    ActiveWindow.ScrollRow = 923
    ActiveWindow.ScrollRow = 929
    ActiveWindow.ScrollRow = 935
    ActiveWindow.ScrollRow = 944
    ActiveWindow.ScrollRow = 950
    ActiveWindow.ScrollRow = 961
    ActiveWindow.ScrollRow = 966
    ActiveWindow.ScrollRow = 972
    ActiveWindow.ScrollRow = 978
    ActiveWindow.ScrollRow = 983
    ActiveWindow.ScrollRow = 987
    ActiveWindow.ScrollRow = 992
    ActiveWindow.ScrollRow = 998
    ActiveWindow.ScrollRow = 1002
    ActiveWindow.ScrollRow = 1005
    ActiveWindow.ScrollRow = 1011
    ActiveWindow.ScrollRow = 1015
    ActiveWindow.ScrollRow = 1020
    ActiveWindow.ScrollRow = 1026
    ActiveWindow.ScrollRow = 1032
    ActiveWindow.ScrollRow = 1039
    ActiveWindow.ScrollRow = 1048
    ActiveWindow.ScrollRow = 1054
    ActiveWindow.ScrollRow = 1061
    ActiveWindow.ScrollRow = 1071
    ActiveWindow.ScrollRow = 1078
    ActiveWindow.ScrollRow = 1084
    ActiveWindow.ScrollRow = 1089
    ActiveWindow.ScrollRow = 1097
    ActiveWindow.ScrollRow = 1102
    ActiveWindow.ScrollRow = 1108
    ActiveWindow.ScrollRow = 1112
    ActiveWindow.ScrollRow = 1115
    ActiveWindow.ScrollRow = 1119
    ActiveWindow.ScrollRow = 1123
    ActiveWindow.ScrollRow = 1127
    ActiveWindow.ScrollRow = 1130
    ActiveWindow.ScrollRow = 1132
    ActiveWindow.ScrollRow = 1134
    ActiveWindow.ScrollRow = 1138
    ActiveWindow.ScrollRow = 1140
    ActiveWindow.ScrollRow = 1143
    ActiveWindow.ScrollRow = 1145
    ActiveWindow.ScrollRow = 1149
    ActiveWindow.ScrollRow = 1151
    ActiveWindow.ScrollRow = 1153
    ActiveWindow.ScrollRow = 1156
    ActiveWindow.ScrollRow = 1160
    ActiveWindow.ScrollRow = 1162
    ActiveWindow.ScrollRow = 1166
    ActiveWindow.ScrollRow = 1169
    ActiveWindow.ScrollRow = 1171
    ActiveWindow.ScrollRow = 1175
    ActiveWindow.ScrollRow = 1177
    ActiveWindow.ScrollRow = 1181
    ActiveWindow.ScrollRow = 1184
    ActiveWindow.ScrollRow = 1186
    ActiveWindow.ScrollRow = 1188
    ActiveWindow.ScrollRow = 1190
    ActiveWindow.ScrollRow = 1192
    ActiveWindow.ScrollRow = 1194
    ActiveWindow.ScrollRow = 1196
    ActiveWindow.ScrollRow = 1199
    ActiveWindow.ScrollRow = 1203
    ActiveWindow.ScrollRow = 1205
    ActiveWindow.ScrollRow = 1209
    ActiveWindow.ScrollRow = 1212
    ActiveWindow.ScrollRow = 1218
    ActiveWindow.ScrollRow = 1220
    ActiveWindow.ScrollRow = 1224
    ActiveWindow.ScrollRow = 1227
    ActiveWindow.ScrollRow = 1231
    ActiveWindow.ScrollRow = 1235
    ActiveWindow.ScrollRow = 1237
    ActiveWindow.ScrollRow = 1238
    ActiveWindow.ScrollRow = 1244
    ActiveWindow.ScrollRow = 1246
    ActiveWindow.ScrollRow = 1248
    ActiveWindow.ScrollRow = 1251
    ActiveWindow.ScrollRow = 1253
    ActiveWindow.ScrollRow = 1255
    ActiveWindow.ScrollRow = 1259
    ActiveWindow.ScrollRow = 1263
    ActiveWindow.ScrollRow = 1265
    ActiveWindow.ScrollRow = 1268
    ActiveWindow.ScrollRow = 1270
    ActiveWindow.ScrollRow = 1272
    ActiveWindow.ScrollRow = 1274
    ActiveWindow.ScrollRow = 1276
    ActiveWindow.ScrollRow = 1278
    ActiveWindow.ScrollRow = 1281
    ActiveWindow.ScrollRow = 1283
    ActiveWindow.ScrollRow = 1285
    ActiveWindow.ScrollRow = 1287
    ActiveWindow.ScrollRow = 1291
    ActiveWindow.ScrollRow = 1292
    ActiveWindow.ScrollRow = 1294
    ActiveWindow.ScrollRow = 1296
    ActiveWindow.ScrollRow = 1298
    ActiveWindow.ScrollRow = 1300
    ActiveWindow.ScrollRow = 1302
    ActiveWindow.ScrollRow = 1304
    ActiveWindow.ScrollRow = 1306
    ActiveWindow.ScrollRow = 1309
    ActiveWindow.ScrollRow = 1311
    ActiveWindow.ScrollRow = 1315
    ActiveWindow.ScrollRow = 1317
    ActiveWindow.ScrollRow = 1319
    ActiveWindow.ScrollRow = 1320
    ActiveWindow.ScrollRow = 1322
    ActiveWindow.ScrollRow = 1324
    ActiveWindow.ScrollRow = 1326
    ActiveWindow.ScrollRow = 1330
    ActiveWindow.ScrollRow = 1332
    ActiveWindow.ScrollRow = 1335
    ActiveWindow.ScrollRow = 1339
    ActiveWindow.ScrollRow = 1341
    ActiveWindow.ScrollRow = 1343
    ActiveWindow.ScrollRow = 1347
    ActiveWindow.ScrollRow = 1350
    ActiveWindow.ScrollRow = 1352
    ActiveWindow.ScrollRow = 1358
    ActiveWindow.ScrollRow = 1360
    ActiveWindow.ScrollRow = 1363
    ActiveWindow.ScrollRow = 1367
    ActiveWindow.ScrollRow = 1371
    ActiveWindow.ScrollRow = 1376
    ActiveWindow.ScrollRow = 1378
    ActiveWindow.ScrollRow = 1382
    ActiveWindow.ScrollRow = 1384
    ActiveWindow.ScrollRow = 1388
    ActiveWindow.ScrollRow = 1391
    ActiveWindow.ScrollRow = 1395
    ActiveWindow.ScrollRow = 1397
    ActiveWindow.ScrollRow = 1399
    ActiveWindow.ScrollRow = 1401
    ActiveWindow.ScrollRow = 1402
    ActiveWindow.ScrollRow = 1404
    ActiveWindow.ScrollRow = 1406
    ActiveWindow.ScrollRow = 1408
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollRow = 1408
    ActiveWindow.ScrollRow = 1406
    ActiveWindow.ScrollRow = 1404
    ActiveWindow.ScrollRow = 1402
    ActiveWindow.ScrollRow = 1401
    ActiveWindow.ScrollRow = 1399
    ActiveWindow.ScrollRow = 1397
    ActiveWindow.ScrollRow = 1395
    ActiveWindow.ScrollRow = 1393
    ActiveWindow.ScrollRow = 1391
    ActiveWindow.ScrollRow = 1389
    ActiveWindow.ScrollRow = 1388
    ActiveWindow.ScrollRow = 1386
    ActiveWindow.ScrollRow = 1384
    ActiveWindow.ScrollRow = 1382
    ActiveWindow.ScrollRow = 1380
    ActiveWindow.ScrollRow = 1378
    ActiveWindow.ScrollRow = 1376
    ActiveWindow.ScrollRow = 1373
    ActiveWindow.ScrollRow = 1369
    ActiveWindow.ScrollRow = 1367
    ActiveWindow.ScrollRow = 1365
    ActiveWindow.ScrollRow = 1363
    ActiveWindow.ScrollRow = 1361
    ActiveWindow.ScrollRow = 1360
    ActiveWindow.ScrollRow = 1358
    ActiveWindow.ScrollRow = 1356
    ActiveWindow.ScrollRow = 1354
    ActiveWindow.ScrollRow = 1352
    ActiveWindow.ScrollRow = 1350
    ActiveWindow.ScrollRow = 1348
    ActiveWindow.ScrollRow = 1347
    ActiveWindow.ScrollRow = 1345
    ActiveWindow.ScrollRow = 1343
    ActiveWindow.ScrollRow = 1341
    ActiveWindow.ScrollRow = 1339
    ActiveWindow.ScrollRow = 1337
    ActiveWindow.ScrollRow = 1335
    ActiveWindow.ScrollRow = 1333
    ActiveWindow.ScrollRow = 1332
    ActiveWindow.ScrollRow = 1330
    ActiveWindow.ScrollRow = 1328
    ActiveWindow.ScrollRow = 1326
    ActiveWindow.ScrollRow = 1324
    ActiveWindow.ScrollRow = 1322
    ActiveWindow.ScrollRow = 1320
    ActiveWindow.ScrollRow = 1319
    ActiveWindow.ScrollRow = 1317
    ActiveWindow.ScrollRow = 1315
    ActiveWindow.ScrollRow = 1313
    ActiveWindow.ScrollRow = 1311
    ActiveWindow.ScrollRow = 1309
    ActiveWindow.ScrollRow = 1307
    ActiveWindow.ScrollRow = 1306
    ActiveWindow.ScrollRow = 1304
    ActiveWindow.ScrollRow = 1302
    ActiveWindow.ScrollRow = 1300
    ActiveWindow.ScrollRow = 1298
    ActiveWindow.ScrollRow = 1296
    ActiveWindow.ScrollRow = 1294
    ActiveWindow.ScrollRow = 1292
    ActiveWindow.ScrollRow = 1291
    ActiveWindow.ScrollRow = 1289
    ActiveWindow.ScrollRow = 1287
    ActiveWindow.ScrollRow = 1285
    ActiveWindow.ScrollRow = 1283
    ActiveWindow.ScrollRow = 1281
    ActiveWindow.ScrollRow = 1279
    ActiveWindow.ScrollRow = 1278
    ActiveWindow.ScrollRow = 1276
    ActiveWindow.ScrollRow = 1274
    ActiveWindow.ScrollRow = 1272
    ActiveWindow.ScrollRow = 1270
    ActiveWindow.ScrollRow = 1268
    ActiveWindow.ScrollRow = 1266
    ActiveWindow.ScrollRow = 1265
    ActiveWindow.ScrollRow = 1263
    ActiveWindow.ScrollRow = 1261
    ActiveWindow.ScrollRow = 1259
    ActiveWindow.ScrollRow = 1257
    Range("H1269").Select
    Sheets("Sheet2").Select
    Range("A1:A144").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("H:H").EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=132
    Application.CutCopyMode = False
    Selection.Cut Destination:=Range("H1303:H1446")
    Range("H1303:H1445").Select
    Range("H1445").Activate
    ActiveWindow.SmallScroll Down:=138
    Selection.Cut Destination:=Range("H1294:H1436")
    Range("H1294:H1435").Select
    Range("H1435").Activate
    ActiveWindow.SmallScroll Down:=114
    Selection.Cut Destination:=Range("H1285:H1426")
    Range("H1285:H1425").Select
    Range("H1425").Activate
    ActiveWindow.SmallScroll Down:=117
    Selection.Cut Destination:=Range("H1276:H1416")
    Range("H1275:H1415").Select
    Range("H1415").Activate
    ActiveWindow.SmallScroll Down:=111
    Selection.Cut Destination:=Range("H1266:H1406")
    Range("H1267:H1405").Select
    Range("H1405").Activate
    ActiveWindow.SmallScroll Down:=117
    Selection.Cut Destination:=Range("H1258:H1396")
    Range("H1258:H1395").Select
    Range("H1395").Activate
    ActiveWindow.SmallScroll Down:=114
    Selection.Cut Destination:=Range("H1249:H1386")
    Range("H1249:H1385").Select
    Range("H1385").Activate
    Selection.Cut Destination:=Range("H1239:H1375")
    Range("H1239:H1374").Select
    Selection.Cut Destination:=Range("H1231:H1366")
    Range("H1375").Select
    Selection.Cut Destination:=Range("H1376")
    Range("H1376").Select
    ActiveWindow.SmallScroll Down:=-15
    Range("H1231:H1365").Select
    Range("H1365").Activate
    ActiveWindow.SmallScroll Down:=123
    Selection.Cut Destination:=Range("H1222:H1356")
    Range("H1221:H1355").Select
    Range("H1355").Activate
    ActiveWindow.SmallScroll Down:=102
    Selection.Cut Destination:=Range("H1212:H1346")
    Range("H1213:H1345").Select
    Range("H1345").Activate
    ActiveWindow.SmallScroll Down:=114
    Selection.Cut Destination:=Range("H1204:H1336")
    Range("H1204:H1335").Select
    Range("H1335").Activate
    ActiveWindow.SmallScroll Down:=114
    Selection.Cut Destination:=Range("H1195:H1326")
    Range("H1195:H1325").Select
    Range("H1325").Activate
    ActiveWindow.SmallScroll Down:=114
    Selection.Cut Destination:=Range("H1186:H1316")
    Range("H1186:H1315").Select
    ActiveWindow.SmallScroll Down:=-21
    Selection.Cut Destination:=Range("H1177:H1306")
    Range("H1177:H1305").Select
    Range("H1305").Activate
    ActiveWindow.SmallScroll Down:=120
    Selection.Cut Destination:=Range("H1168:H1296")
    Range("H1168:H1295").Select
    Range("H1295").Activate
    ActiveWindow.SmallScroll Down:=117
    Selection.Cut Destination:=Range("H1159:H1286")
    Range("H1159:H1285").Select
    Range("H1285").Activate
    ActiveWindow.SmallScroll Down:=123
    Selection.Cut Destination:=Range("H1150:H1276")
    Range("H1150:H1275").Select
    Range("H1275").Activate
    ActiveWindow.SmallScroll Down:=99
    Selection.Cut Destination:=Range("H1141:H1266")
    Range("H1141:H1265").Select
    Range("H1265").Activate
    ActiveWindow.SmallScroll Down:=111
    Selection.Cut Destination:=Range("H1132:H1256")
    Range("H1132:H1255").Select
    Range("H1255").Activate
    ActiveWindow.SmallScroll Down:=102
    Selection.Cut Destination:=Range("H1123:H1246")
    Range("H1123:H1245").Select
    Range("H1245").Activate
    ActiveWindow.SmallScroll Down:=120
    Selection.Cut Destination:=Range("H1114:H1236")
    Range("H1114:H1235").Select
    Range("H1235").Activate
    ActiveWindow.SmallScroll Down:=99
    Selection.Cut Destination:=Range("H1105:H1226")
    Range("H1105:H1225").Select
    Range("H1225").Activate
    ActiveWindow.SmallScroll Down:=120
    Selection.Cut Destination:=Range("H1096:H1216")
    Range("H1096:H1215").Select
    Range("H1215").Activate
    ActiveWindow.SmallScroll Down:=114
    Selection.Cut Destination:=Range("H1087:H1206")
    Range("H1087:H1206").Select
End Sub

Sparktracer · Sep 10, 2010

Here is my final addition to the post tonight. In this macro, I opened a new sheet (3). I then opened my master document for my header. Copied rows 1-8 (I only need rows 1-4) and pasted into the new Sheet 3. I then look for data times that are within the conditional formating of S1 column G "WINDOW" (I have changed my format to only highlight 9-18 vs. 8-18 from earlier). I then switch to S2 and locate the rows of data that are in the "window" and highlight all of the desired rows. I then determine from S1 column E, what the temperature was during the desired "window" time. I select only the cold rows from S2 (-40 from S1). I then copy them, skipping blanks, to S3. I do the min, max, average formulas, and then repeat the selecting of just the hot desired rows and past them, skipping blanks, into S3.

Rich (BB code):

Sub Sheet3()
'
' Sheet3 Macro
' Macro recorded 9/10/2010 by Storage Desk
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    Sheets("Sheet2").Select
    Sheets.Add
    Rows("1:8").Select
    Selection.Copy
    Windows("Book3.xls").Activate
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Rows("9:9").Select
    ActiveWindow.FreezePanes = True
    Sheets("Sheet1").Select
    ActiveWindow.SmallScroll Down:=30
    Sheets("Sheet2").Select
    ActiveWindow.SmallScroll Down:=-21
    Range("3:3,5:5,7:7").Select
    Range("A7").Activate
    Sheets("Sheet1").Select
    ActiveWindow.SmallScroll Down:=858
    Sheets("Sheet2").Select
    ActiveWindow.SmallScroll Down:=57
    Range( _
        "3:3,5:5,7:7,91:91,89:89,87:87,85:85,83:83,81:81,79:79,77:77,75:75,73:73,71:71,69:69,67:67,65:65,63:63,61:61,59:59" _
        ).Select
    Range("A59").Activate
    ActiveWindow.SmallScroll Down:=-45
    Union(Range( _
        "33:33,31:31,29:29,27:27,25:25,23:23,21:21,19:19,17:17,15:15,3:3,5:5,7:7,91:91,89:89,87:87,85:85,83:83,81:81,79:79,77:77,75:75,73:73,71:71,69:69,67:67,65:65,63:63,61:61,59:59,57:57,55:55" _
        ), Range("53:53,51:51,49:49,47:47,45:45,43:43,41:41,39:39,37:37,35:35")).Select
    Range("A15").Activate
    ActiveWindow.SmallScroll Down:=-18
    Union(Range( _
        "33:33,31:31,29:29,27:27,25:25,23:23,21:21,19:19,17:17,15:15,13:13,11:11,9:9,3:3,5:5,7:7,91:91,89:89,87:87,85:85,83:83,81:81,79:79,77:77,75:75,73:73,71:71,69:69,67:67,65:65,63:63,61:61" _
        ), Range( _
        "59:59,57:57,55:55,53:53,51:51,49:49,47:47,45:45,43:43,41:41,39:39,37:37,35:35" _
        )).Select
    Range("A9").Activate
    ActiveWindow.SmallScroll Down:=90
    Sheets("Sheet1").Select
    ActiveWindow.SmallScroll Down:=528
    Sheets("Sheet2").Select
    ActiveWindow.SmallScroll Down:=9
    Union(Range( _
        "33:33,31:31,29:29,27:27,25:25,23:23,21:21,19:19,17:17,15:15,13:13,11:11,9:9,144:144,142:142,140:140,138:138,136:136,134:134,132:132,130:130,128:128,126:126,124:124,122:122,120:120,118:118,116:116,114:114,112:112,110:110,108:108" _
        ), Range( _
        "106:106,104:104,102:102,3:3,5:5,7:7,91:91,89:89,87:87,85:85,83:83,81:81,79:79,77:77,75:75,73:73,71:71,69:69,67:67,65:65,63:63,61:61,59:59,57:57,55:55,53:53,51:51,49:49,47:47,45:45,43:43,41:41" _
        ), Range("39:39,37:37,35:35")).Select
    Range("A102").Activate
    ActiveWindow.SmallScroll Down:=-21
    Union(Range( _
        "33:33,31:31,29:29,27:27,25:25,23:23,21:21,19:19,17:17,15:15,13:13,11:11,9:9,144:144,142:142,140:140,138:138,136:136,134:134,132:132,130:130,128:128,126:126,124:124,122:122,120:120,118:118,116:116,114:114,112:112,110:110,108:108" _
        ), Range( _
        "106:106,104:104,102:102,100:100,98:98,96:96,94:94,3:3,5:5,7:7,91:91,89:89,87:87,85:85,83:83,81:81,79:79,77:77,75:75,73:73,71:71,69:69,67:67,65:65,63:63,61:61,59:59,57:57,55:55,53:53,51:51,49:49" _
        ), Range("47:47,45:45,43:43,41:41,39:39,37:37,35:35")).Select
    Range("A94").Activate
    With Selection.Interior
        .ColorIndex = 35
        .Pattern = xlSolid
    End With
    Sheets("Sheet2").Select
    ActiveWindow.SmallScroll Down:=24
    Range( _
        "144:144,140:140,136:136,132:132,128:128,124:124,120:120,116:116,112:112,108:108,104:104" _
        ).Select
    Range("A104").Activate
    ActiveWindow.SmallScroll Down:=-36
    Range( _
        "144:144,140:140,136:136,132:132,128:128,124:124,120:120,116:116,112:112,108:108,104:104,100:100,96:96,91:91,87:87,83:83,79:79,75:75,71:71,67:67" _
        ).Select
    Range("A67").Activate
    ActiveWindow.SmallScroll Down:=-45
    Range( _
        "144:144,140:140,136:136,132:132,128:128,124:124,120:120,116:116,112:112,108:108,104:104,100:100,96:96,91:91,87:87,83:83,79:79,75:75,71:71,67:67,63:63,59:59,55:55,51:51,47:47,43:43,39:39,35:35,31:31,27:27,23:23" _
        ).Select
    Range("A23").Activate
    ActiveWindow.SmallScroll Down:=-42
    Union(Range( _
        "15:15,11:11,7:7,3:3,144:144,140:140,136:136,132:132,128:128,124:124,120:120,116:116,112:112,108:108,104:104,100:100,96:96,91:91,87:87,83:83,79:79,75:75,71:71,67:67,63:63,59:59,55:55,51:51,47:47,43:43,39:39,35:35" _
        ), Range("31:31,27:27,23:23,19:19")).Select
    Range("A3").Activate
    Selection.Copy
    Sheets("Sheet4").Select
    Rows("9:9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    ActiveWindow.SmallScroll Down:=21
    Range("B45").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "min"
    Range("B46").Select
    ActiveCell.FormulaR1C1 = "max"
    Range("B47").Select
    ActiveCell.FormulaR1C1 = "avg"
    Range("C45").Select
    ActiveCell.FormulaR1C1 = "=MIN(R[-36]C:R[-1]C)"
    Range("C46").Select
    ActiveCell.FormulaR1C1 = "=MAX(R[-37]C:R[-2]C)"
    Range("C47").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[-38]C:R[-3]C)"
    Range("B45:C47").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    ActiveWindow.SmallScroll Down:=12
    Range("C45:C47").Select
    Selection.AutoFill Destination:=Range("C45:CT47"), Type:=xlFillDefault
    Range("C45:CT47").Select
    Range("A48:CW48").Select
    Range("CW48").Activate
    With Selection.Interior
        .ColorIndex = 1
        .Pattern = xlSolid
    End With
    Range("A45:A47").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection.Interior
        .ColorIndex = 8
        .Pattern = xlSolid
    End With
    ActiveCell.FormulaR1C1 = "COLD"
    Rows("45:47").Select
    Range("B45").Activate
    Selection.Copy
    Rows("49:49").Select
    ActiveSheet.Paste
    Range("A49:A51").Select
    Selection.Interior.ColorIndex = 44
    Range("A49:A51").Select
    ActiveCell.FormulaR1C1 = "HOT"
    Sheets("Sheet2").Select
    ActiveWindow.SmallScroll Down:=-18
    Range("5:5,9:9,13:13,17:17,21:21,25:25,29:29,33:33,37:37,41:41,45:45").Select
    Range("A45").Activate
    ActiveWindow.SmallScroll Down:=42
    Range( _
        "5:5,9:9,13:13,17:17,21:21,25:25,29:29,33:33,37:37,41:41,45:45,49:49,53:53,57:57,61:61,65:65,69:69,73:73,77:77,81:81,85:85,89:89" _
        ).Select
    Range("A89").Activate
    ActiveWindow.SmallScroll Down:=42
    Range( _
        "5:5,9:9,13:13,17:17,21:21,25:25,29:29,33:33,37:37,41:41,45:45,49:49,53:53,57:57,61:61,65:65,69:69,73:73,77:77,81:81,85:85,89:89,94:94,98:98,102:102,106:106,110:110,114:114,118:118,122:122" _
        ).Select
    Range("A122").Activate
    ActiveWindow.SmallScroll Down:=21
    Union(Range( _
        "134:134,138:138,142:142,5:5,9:9,13:13,17:17,21:21,25:25,29:29,33:33,37:37,41:41,45:45,49:49,53:53,57:57,61:61,65:65,69:69,73:73,77:77,81:81,85:85,89:89,94:94,98:98,102:102,106:106,110:110,114:114,118:118" _
        ), Range("122:122,126:126,130:130")).Select
    Range("A142").Activate
    Selection.Copy
    Sheets("Sheet4").Select
    Range("A52").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy h:mm:ss"
    Range("C49").Select
    ActiveCell.FormulaR1C1 = "=MIN(R[3]C:R[37]C)"
    Range("C50").Select
    ActiveWindow.SmallScroll Down:=-18
    ActiveCell.FormulaR1C1 = "=MAX(R[2]C:R[36]C)"
    Range("C51").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[1]C:R[35]C)"
    Range("C52").Select
    ActiveWindow.SmallScroll Down:=-15
    Range("C49:C51").Select
    Selection.AutoFill Destination:=Range("C49:CT51"), Type:=xlFillDefault
    Range("C49:CT51").Select
    Columns("C:CT").Select
    Range("CT3").Activate
    ActiveWindow.SmallScroll Down:=-54
    ActiveWindow.SmallScroll Down:=21
    Selection.AutoFill Destination:=Range("C49:CT51"), Type:=xlFillDefault
    Range("C49:CT51").Select
    ActiveWindow.SmallScroll Down:=21
    Range("C9:CT86").Select
    Range("CT86").Activate
    Selection.NumberFormat = "0.000_);[Red](0.000)"
    ActiveWindow.SmallScroll Down:=18
    Range("A45:A47").Select
End Sub

Sparktracer · Oct 18, 2010

Zack has done a wonderful job and will be posting the final revisions to our problem soon. Hopefully with a little better explination as to what it was that we did.

Zack, you are my hero.

Thanks for all your hard work.

Zack Barresse · Oct 18, 2010

The code finally resided in an add-in with a userform and three modules (menu, functions, importing). The only controls within the usefrom needs two textboxes (tbFile1 and tbFile2) and four buttons, two for browsing for selecting files (cmbBrowse1 and cmbBrowse2), one for cancelling the form (cmbCancel) and one for starting the import action (cmbCombine). The userform is called frmCombine.

frmCombine code:

Code:

Option Explicit

Dim vVal As Variant
Dim sPrompt As String

Const sFilterCSV As String = "Excel CSV Files (*.csv), *.csv"
Const sFilterDAT As String = "DAT Files (*.dat), *.dat"

Private Sub cmbBrowse1_Click()
    Dim sP As String
    Call GetFile(Me.tbFile1, sFilterCSV)
    If Me.tbFile1.Value = "" Then Exit Sub
    If UCase(Right(Me.tbFile1.Value, 3)) <> "CSV" Then
        sPrompt = "The specified file type is not consistent with the standard CSV file type!" & vbNewLine & vbNewLine & "Continue?"
        If MsgBox(sPrompt, vbInformation, "CAUTION!") <> vbYes Then
            Me.tbFile1.Value = ""
        End If
    End If
    sP = CheckForFile(GetPath(Me.tbFile1.Value), Me.tbFile1.Value)
    If sP = "ERROR" Then
        MsgBox "There was an error in finding a matching file.  Please use the browse button" & vbNewLine & _
            "in the next file area to choose the correct file.", vbOKOnly, "ERROR MATCHING FILE"
    Else
        Me.tbFile2.Value = sP
    End If
End Sub

Private Sub cmbBrowse2_Click()
    Call GetFile(Me.tbFile2)
    If Me.tbFile2.Value = "" Then Exit Sub
''''''''''    If UCase(Right(Me.tbFile2.Value, 3)) <> "DAT" Then
''''''''''        sPrompt = "The specified file type is not consistent with the standard DAT file type!" & vbNewLine & vbNewLine & "Continue?"
''''''''''        If MsgBox(sPrompt, vbInformation, "CAUTION!") <> vbYes Then
''''''''''            Me.tbFile2.Value = ""
''''''''''        End If
''''''''''        MsgBox "The specified file type is not consistent with the standard DAT file type!", vbInformation, "CAUTION!"
''''''''''    End If
End Sub

Private Function GetFile(oCtrl As Control, Optional sType As String)
    'use one function so both controls can utilize the same code.
    'use variable to get file name, setting the correct filter to each.
    vVal = Application.GetOpenFilename(FileFilter:=sType, MultiSelect:=False)
    If vVal = False Then
        'cancel was pressed
        Exit Function
    End If
    'set control value to full file name returned from user selection.
    oCtrl.Value = vVal
End Function

Private Sub cmbCancel_Click()
    Unload Me
End Sub

Private Sub cmbCombine_Click()
    
    Dim S1 As Workbook, S2 As Workbook
    Dim wsS1 As Worksheet, wsS2 As Worksheet
    Dim sS1Name As String, sS2Name As String
    Dim sS1Path As String, sS2Path As String
    Dim bS1Open As Boolean, bS2Open As Boolean
    
    'check if selection is an actual file or if it's blank
    If Dir(Me.tbFile1.Value) = "" Or Me.tbFile1.Value = "" Then
        MsgBox "The first file is not a good path.", vbInformation, "ERROR!"
        Exit Sub
    End If
    'check if selection is an actual file or if it's blank
    If Dir(Me.tbFile2.Value) = "" Or Me.tbFile2.Value = "" Then
        MsgBox "The second file is not a good path.", vbInformation, "ERROR!"
        Exit Sub
    End If
    'check if the same files were chosen
    If Me.tbFile1.Value = Me.tbFile2.Value Then
        MsgBox "You must select two DIFFERENT files.", vbInformation, "ERROR!"
        Exit Sub
    End If
    
    'set file names and paths to variables
    sS1Name = Right(Me.tbFile1.Value, Len(Me.tbFile1.Value) - InStrRev(Me.tbFile1.Value, Application.PathSeparator))
    sS1Path = Left(Me.tbFile1.Value, Len(Me.tbFile1.Value) - Len(sS1Name))
    sS2Name = Right(Me.tbFile2.Value, Len(Me.tbFile2.Value) - InStrRev(Me.tbFile2.Value, Application.PathSeparator))
    sS2Path = Left(Me.tbFile2.Value, Len(Me.tbFile2.Value) - Len(sS2Name))
    
    'check if the first workbook is open.  if so, set it to a variable, otherwise open it
    If ISWBOPEN(sS1Name) = True Then
        Set S1 = Workbooks(sS1Name)
        bS1Open = True
    Else
        Set S1 = Workbooks.Open(sS1Path & sS1Name)
        bS1Open = False
    End If
    
    'check if the second workbook is open.  if so, set it to a variable, otherwise open it
    If ISWBOPEN(sS2Name) = True Then
        Set S2 = Workbooks(sS2Name)
        bS2Open = True
    Else
        Set S2 = Workbooks.Open(sS2Path & sS2Name)
        bS2Open = False
    End If
    
    'hide the userform while running
    Me.Hide
    
    'call the routine to do actual import, housed in a standard module
    Call ImportData(S1.Sheets(1), S2.Sheets(1))
    
    'Close and do NOT save changes
    If bS1Open = False Then S1.Close False
'    If bS2Open = False Then S2.Close False 'closed in temporary format

    'tell the user it's complete, then close userform
    If bValidC = False And bValidH = False Then
        MsgBox "There was no data to show!", vbExclamation, "ERROR!"
        On Error Resume Next
        S2.Close False
    Else
        MsgBox "The specified files have been combined and are ready for viewing!" & _
            vbNewLine & vbNewLine & "The new file is NOT yet saved.", vbExclamation, "INFO!"
    End If
    Unload Me
    
End Sub

''Private Sub UserForm_Initialize()
''    'for testing purposes
''    Me.tbFile1.Value = "C:\Users\Z\Desktop\Spacetracker\testdata\TR14061 chamber 9-10-2010.csv"
''    Me.tbFile2.Value = "C:\Users\Z\Desktop\Spacetracker\testdata\TR14061 TS DC 3. 23A.dat"
''End Sub

Standard module (modMenu) code:

Code:

Option Explicit

'Change as desired
Const sBtnName As String = "Combine Files"
Const iPosition As Long = 1

Sub CreateButton()

    Dim cbTools As CommandBar, cbBtn As CommandBarButton
    
    'set variable to Tools command bar
    Set cbTools = Application.CommandBars("Tools")
    
    'delete the menu if it was already created, so we don't have duplicates
    Call DeleteButton
    
    'create the button at specified position
    Set cbBtn = cbTools.Controls.Add(Type:=msoControlButton, Before:=iPosition, Temporary:=False)
    
    'set button properties
    cbBtn.Caption = sBtnName
    cbBtn.DescriptionText = sBtnName
    cbBtn.FaceId = 472    'whatever faceID you want
    cbBtn.OnAction = "ShowMyUserform"
    
End Sub

Sub DeleteButton()
    'ignores errors and deletes the toolbar button if it exists.
    On Error Resume Next
    Application.CommandBars("Tools").Controls(sBtnName).Delete
    On Error GoTo 0
End Sub

Sub ShowMyUserform()
    frmCombine.Show
End Sub

Standard module (modFunctions) code:

Code:

Option Explicit

Function GetPath(strFullName As String)
    If Right(strFullName, 1) = Application.PathSeparator Then
        GetPath = strFullName
        Exit Function
    End If
    If InStr(1, strFullName, Application.PathSeparator) = 0 Then
        GetPath = "INVALID!"
        Exit Function
    End If
    GetPath = Left(strFullName, InStrRev(strFullName, Application.PathSeparator))
    If GetPath = vbNullString Then GetPath = "ERROR!"
End Function

Function CheckForFile(sPathCheck As String, sPathTest As String) As String
    Dim vDateTest As Variant, vDateCheck As Variant, sNameTemp As String
    Dim oFile As Object, FSO As Object, oFolder As Object
    Dim oTemp As Object, oTempCheck As Object
    Dim dtTest As Date, dtCheck As Date
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oTemp = FSO.GetFile(sPathTest)
    vDateTest = oTemp.DateCreated
    dtTest = DateValue(CStr(Trim(Left(CStr(vDateTest), InStr(1, CStr(vDateTest), " ")))))
    sNameTemp = Right(sPathTest, Len(sPathTest) - InStrRev(sPathTest, Application.PathSeparator))
    'loop through files
    Set oFolder = FSO.GetFolder(sPathCheck)
    For Each oFile In oFolder.Files
        vDateCheck = oFile.DateCreated
        dtCheck = DateValue(CStr(Trim(Left(CStr(vDateCheck), InStr(1, CStr(vDateCheck), " ")))))
'        Debug.Print oTemp.Name & "(" & dtTest & ") | " & oFile.Name & "(" & dtCheck & ")"
        If dtTest = dtCheck And UCase(sNameTemp) <> UCase(oFile.Name) Then
            vDateCheck = dtCheck 'FileDateTime(oFile.Path)
            Set oTempCheck = oFile
            Exit For
        End If
    Next oFile
    If IsEmpty(vDateCheck) = True Then
        'not found automatically
        CheckForFile = "ERROR"
    Else
        'found here
        CheckForFile = oTempCheck.Path
    End If
    Set oTemp = Nothing
    Set oTempCheck = Nothing
    Set oFile = Nothing
    Set FSO = Nothing
End Function

Public Sub TOGGLEEVENTS(blnState As Boolean)
'Originally written by Zack Barresse
    With Application
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        If blnState Then .CutCopyMode = False
        If blnState Then .StatusBar = False
    End With
End Sub

Public Function ISWBOPEN(wbName As String) As Boolean
'Originally found written by Jake Marx
    On Error Resume Next
    ISWBOPEN = Len(Workbooks(wbName).Name)
End Function

Standard module (modImport) code:

Code:

Option Explicit

#Const DEBUGGING = True

Public bValidH As Boolean
Public bValidC As Boolean

Dim wbTemp  As Workbook
Dim wbTemp2 As Workbook
Dim wsHot   As Worksheet
Dim wsCold  As Worksheet
Dim ws1New  As Worksheet
Dim ws2New  As Worksheet
Dim rFind   As Range
Dim rLoop   As Range
Dim rCold   As Range
Dim rHot    As Range
Dim iRow    As Long
Dim iCnt    As Long
Dim iLoad   As Long
Dim iHot    As Long
Dim iCold   As Long
Dim iStart  As Long
Dim iLast   As Long
Dim iEnd    As Long
Dim iTotal  As Long
Dim i       As Long
Dim dtTemp  As Date
Dim dtLo    As Date
Dim dtHi    As Date
Dim sDate   As String
Dim sTempP  As String
Dim sTempF  As String
Dim bHot    As Boolean
Dim bLoad   As Boolean
Dim vValD   As Variant
Dim vValT   As Variant

Const iLower            As Long = 9
Const iUpper            As Long = 18
Const sNumFormat        As String = "0.000_);[Red](0.000)"
Const sDateFormat       As String = "m/d/yyyy h:mm"
Const sDateFormatFinal  As String = "m/d/yyyy hh:mm:ss"
Const sDateOnlyFormat   As String = "m/d/yyyy"
Const sTimeOnlyFormat   As String = "h:mm"

Sub ImportData(WS1 As Worksheet, WS2 As Worksheet, Optional wksDest As Worksheet)

    'Turn off events for efficiency and warnings suppressed
    #If DEBUGGING = False Then
        Call TOGGLEEVENTS(False)
    #End If
    
    'make a new workbook
    If wksDest Is Nothing Then
        Set wksDest = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    End If
    
    'save second file temporarily as an excel file for ease of use
    WS2.SaveAs WS2.Parent.FullName & ".xls"
    sTempP = WS2.Parent.Path
    sTempF = WS2.Parent.Name
    Set wbTemp2 = Workbooks(sTempF)
    Set WS2 = wbTemp2.Sheets(1) 're-dim sheet here
    WS2.Name = "temp"
    
    'create worksheet for final data destination dump
    Set wbTemp = wksDest.Parent
    
    'copy sheets over to new workbook
    WS2.Copy Before:=wbTemp.Sheets(1)
    WS1.Copy Before:=wbTemp.Sheets(1)
    Set ws1New = wbTemp.Sheets(1)
    Set ws2New = wbTemp.Sheets(2)
    
    'set a few variables for later use
    iLoad = 1
    iHot = 0
    iCold = 0
    bLoad = False
    
    'set worksheets for data dump for both hot and cold
    Set wsHot = wbTemp.Sheets.Add
    Set wsCold = wbTemp.Sheets.Add
    wsHot.Name = "Hot Data"
    wsCold.Name = "Cold Data"
    wksDest.Name = "Combined Data"
    
    'format columns ready for date/time entry
    ws1New.Columns(1).NumberFormat = sDateOnlyFormat '"d/m/yyyy"
    ws1New.Columns(2).NumberFormat = sTimeOnlyFormat '"h:mm"
    ws2New.Columns(1).NumberFormat = sDateFormat
    
    'start looping through CSV file, starting from row 4 until the last
    'row with data
    iTotal = ws1New.Cells(ws1New.Rows.Count, 1).End(xlUp).Row
    
'    On Error Resume Next
    On Error GoTo ErrHandler
    
    'insert formulas on the second worksheet
    ws2New.Columns(2).Insert Shift:=xlToRight
    iLast = ws2New.Cells(ws2New.Rows.Count, 1).End(xlUp).Row
    ws2New.Range("B1:B" & iLast).Formula = "=DATE(YEAR(A1),MONTH(A1),DAY(A1))+TIME(HOUR(A1),MINUTE(A1),0)"
'    Application.CutCopyMode = False
    
    'insert formulas on the first worksheet (csv file)
    iLast = ws1New.Cells(ws1New.Rows.Count, 1).End(xlUp).Row
    ws1New.Range("F4:F" & iLast).Formula = "=DATE(YEAR(A4),MONTH(A4),DAY(A4))+TIME(HOUR(B4),MINUTE(B4),0)"
    ws1New.Range("G4:G" & iLast).Formula = "=IF(ROW()<>4,IF(E4=E3,"""",""xfer""),"""")"
    ws1New.Range("H4:H" & iLast).Formula = "=IF(COUNTIF($G$4:G4,""xfer"")=0,"""",IF(G4=""xfer"",0,H3+1))"
    ws1New.Range("I4:I" & iLast).Formula = "=IF(ISNA(MATCH(F4,temp!B:B,0)),0,IF(AND(H4>=" & iLower & ",H4<=" & iUpper & "),MATCH(F4,temp!B:B,0),0))"
    ws1New.Range("J4:J" & iLast).Formula = "=IF(ISNA(MATCH(F4,temp!B:B,0)),0,INDEX(temp!B:B,MATCH(F4,temp!B:B,0),1))"
    
    'trap for a possible first qualifying xfer match
    For i = 4 To 100
        If ws1New.Cells(i, 7).Value = "xfer" Then Exit For
    Next i
    If i <> 100 Then
        iCnt = 20
        For iRow = i - 1 To 4 Step -1
            ws1New.Cells(iRow, 8).Value = iCnt
            iCnt = iCnt - 1
        Next iRow
    End If
    ''previous attempt...
'    iRow = 4
'    iCnt = 0
'    Do Until iCnt > 0
'        If Len(ws1New.Cells(iRow, 8).Value) > 0 Then
'            iCnt = iRow - 1
'        End If
'        iRow = iRow + 1
'    Loop
'    iRow = WorksheetFunction.CountIf(ws1New.Range("E4:E" & iCnt), ws1New.Cells(iCnt, 5).Value)
'    If iRow >= 19 Then
'        For i = 4 To iCnt
'            ws1New.Cells(i, 8).Value = i - 3
'        Next i
'    End If
    
    'autofilter first worksheet
    iLast = ws1New.Cells(ws1New.Rows.Count, 1).End(xlUp).Row
    With ws1New.Range("A3:J" & iLast)
        .AutoFilter Field:=8, Criteria1:=">=" & iLower, Operator:=xlAnd, Criteria2:="<=" & iUpper
        With Intersect(.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Cells, ws1New.Columns(8))
            .SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 33
        End With
        .AutoFilter Field:=9, Criteria1:=">0"
        With Intersect(.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Cells, ws1New.Columns(9))
            .SpecialCells(xlCellTypeVisible).Interior.ColorIndex = 33
        End With
    End With
    
    'copy cold data
    ws1New.Range("A3:J" & iLast).AutoFilter Field:=5, Criteria1:="<0"
    iRow = 0
    iCnt = 1
    iLast = ws1New.Cells(ws1New.Rows.Count, 1).End(xlUp).Row
    For Each rCold In ws1New.Range("J4:J" & iLast).SpecialCells(xlCellTypeVisible)
        iRow = WorksheetFunction.Match(rCold.Value, ws2New.Columns(2), 0)
        If iRow <> 0 Then
            wsCold.Range("A" & iCnt).Resize(1, 102).Value = ws2New.Range("A" & iRow & ":CW" & iRow).Value
            iCnt = iCnt + 1
        End If
        iRow = 0
    Next rCold
    wsCold.Columns(2).Delete
    
    'copy hot data
    iRow = 0
    iCnt = 1
    ws1New.Range("A3:J" & iLast).AutoFilter Field:=5, Criteria1:=">0"
    iLast = ws1New.Cells(ws1New.Rows.Count, 1).End(xlUp).Row
    For Each rHot In ws1New.Range("J4:J" & iLast).SpecialCells(xlCellTypeVisible)
        iRow = WorksheetFunction.Match(rHot.Value, ws2New.Columns(2), 0)
        If iRow <> 0 Then
            wsHot.Range("A" & iCnt).Resize(1, 102).Value = ws2New.Range("A" & iRow & ":CW" & iRow).Value
            iCnt = iCnt + 1
        End If
        iRow = 0
    Next rHot
    wsHot.Columns(2).Delete
    
    'check if either worksheet has data in it
    'set variables to true first
    'if neither worksheet has data, just close the file and exit right now
    bValidC = True
    bValidH = True
    If WorksheetFunction.CountA(wsCold.Cells) = 0 Then bValidC = False
    If WorksheetFunction.CountA(wsHot.Cells) = 0 Then bValidH = False
    If bValidC = False And bValidH = False Then
        wksDest.Parent.Close False
        GoTo ExitNow
    End If
    
    'the template sheet was copied to this workbook in order to copy it to the destination
    'workbook, thus any changes that should be made can be done once.
    Template.Range("1:4").Copy wksDest.Range("1:4")
    Application.CutCopyMode = False
    
    'copy cold data
    iRow = 5
    iCold = WorksheetFunction.Count(wsCold.Columns(1))
    If bValidC = True Then
        With wsCold.Range("A1:CW" & wsCold.Cells(wsCold.Rows.Count, 1).End(xlUp).Row)
            wksDest.Range("A5:CW" & iCold + 4).Value = .Value
        End With
        
        'calculations
        '   headers
        '       cold
        wksDest.Cells(iCold + 5, 1).Value = "COLD"
        wksDest.Range("A" & iCold + 5 & ":A" & iCold + 7).MergeCells = True
        wksDest.Range("A" & iCold + 5 & ":A" & iCold + 7).HorizontalAlignment = xlCenter
        wksDest.Range("A" & iCold + 5 & ":A" & iCold + 7).VerticalAlignment = xlCenter
        wksDest.Range("A" & iCold + 5 & ":A" & iCold + 7).Interior.ColorIndex = 8
        wksDest.Cells(iCold + 5, 2).Value = "Min"
        wksDest.Cells(iCold + 6, 2).Value = "Max"
        wksDest.Cells(iCold + 7, 2).Value = "Average"
    End If
    
    If bValidH = True Then
        '       hot
        wksDest.Cells(iCold + 9, 1).Value = "HOT"
        wksDest.Range("A" & iCold + 9 & ":A" & iCold + 11).MergeCells = True
        wksDest.Range("A" & iCold + 9 & ":A" & iCold + 11).HorizontalAlignment = xlCenter
        wksDest.Range("A" & iCold + 9 & ":A" & iCold + 11).VerticalAlignment = xlCenter
        wksDest.Range("A" & iCold + 9 & ":A" & iCold + 11).Interior.ColorIndex = 44
        wksDest.Cells(iCold + 9, 2).Value = "Average"
        wksDest.Cells(iCold + 10, 2).Value = "Max"
        wksDest.Cells(iCold + 11, 2).Value = "Min"
    End If
    
    If bValidC = True Then
        '   cold formulas
        wksDest.Range("C" & iCold + 5).Resize(1, 96).Formula = "=MIN(C5:C" & iCold + 4 & ")"
        wksDest.Range("C" & iCold + 6).Resize(1, 96).Formula = "=MAX(C5:C" & iCold + 4 & ")"
        wksDest.Range("C" & iCold + 7).Resize(1, 96).Formula = "=AVERAGE(C5:C" & iCold + 4 & ")"
    End If
    
    'get new start row
    iHot = WorksheetFunction.Count(wsHot.Columns(1))
    With wksDest
        iStart = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
        iEnd = iStart + iHot - 1
    End With
    
    If bValidH = True Then
        'copy hot data
        With wsHot.Range("A1:CW" & wsHot.Cells(wsHot.Rows.Count, 1).End(xlUp).Row)
            wksDest.Range("A" & iStart & ":CW" & iEnd).Value = .Value
        End With
        
        '   hot formulas
        wksDest.Range("C" & iCold + 9).Resize(1, 96).Formula = "=AVERAGE(C" & iStart & ":C" & iEnd & ")"
        wksDest.Range("C" & iCold + 10).Resize(1, 96).Formula = "=MAX(C" & iStart & ":C" & iEnd & ")"
        wksDest.Range("C" & iCold + 11).Resize(1, 96).Formula = "=MIN(C" & iStart & ":C" & iEnd & ")"
    End If
    
    'format cell number format
    wksDest.Range("C5:CW" & iEnd).NumberFormat = sNumFormat
    wksDest.Columns(1).NumberFormat = sDateFormatFinal
    wksDest.Cells.Font.Name = "Arial"
    wksDest.Cells.Font.Size = 10
    
    'black line between data and border data
    If bValidC = True And bValidH = True Then
        With wksDest.Range("A" & iCold + 8)
            .Resize(1, 101).Interior.ColorIndex = 1
            Call BorderTheArea(.Offset(-3, 0).Resize(7, 101))
        End With
    End If
    
    'resize columns
    wksDest.Range("A:CW").EntireColumn.AutoFit
    
    'format worksheets used for data collection
    wsHot.Cells.Font.Name = "Arial"
    wsHot.Cells.Font.Size = 10
    wsCold.Cells.Font.Name = "Arial"
    wsCold.Cells.Font.Size = 10
    wsCold.Columns(1).NumberFormat = sDateFormatFinal
    wsHot.Columns(1).NumberFormat = sDateFormatFinal
    wsHot.Cells.EntireColumn.AutoFit
    wsCold.Cells.EntireColumn.AutoFit
    
    'copy data sheets over, format
    With ws1New.Range("J4:J" & ws1New.Cells(ws1New.Rows.Count, 10).End(xlUp).Row)
        .EntireColumn.NumberFormat = "General"
        .SpecialCells(xlCellTypeVisible).NumberFormat = sDateFormatFinal
    End With
    ws1New.AutoFilterMode = False
    With ws1New.Range("A3:J" & ws1New.Cells(ws1New.Rows.Count, 1).End(xlUp).Row)
        .AutoFilter Field:=10, Criteria1:=">0"
        With Intersect(.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count), .Parent.Columns(10))
            .Interior.ColorIndex = 34
        End With
    End With
    ws1New.AutoFilterMode = False
    
    'add formula and format range
    ws1New.Range("K4:K" & ws1New.Cells(ws1New.Rows.Count, 10).End(xlUp).Row).Formula = "=IF(AND(J4<>0,I4<>0),J4,"""")"
    ws1New.Range("K4:K" & ws1New.Cells(ws1New.Rows.Count, 10).End(xlUp).Row).NumberFormat = sDateFormatFinal
    
    'add headers and formats
    ws1New.Range("F3").Value = "XFER"
    ws1New.Range("F3:K3").Value = Array("Date + Time", "XFER", "Series #", "Matched Row", "Returned Vals", "Transferred Vals")
    ws1New.Range("F3:K3").HorizontalAlignment = xlCenter
    ws1New.Columns(6).NumberFormat = sDateFormatFinal
    ws2New.Columns(2).Interior.ColorIndex = 6
    
    'autofit data
    ws2New.Cells.EntireColumn.AutoFit
    
    'hide column B on S2 data (used with formulas from S1 to match data)
    ws2New.Columns(2).Hidden = True
    
    'move worksheets around
    ws2New.Move Before:=wbTemp.Sheets(1)
    ws1New.Move Before:=wbTemp.Sheets(1)
    
    'autofit cells
    wsHot.Cells.EntireColumn.AutoFit
    wsCold.Cells.EntireColumn.AutoFit
    
    'filter values in S1 data for only values which have been transferred to final sheet
    With ws1New.Range("A3:K" & ws1New.Cells(ws1New.Rows.Count, 1).End(xlUp).Row)
        .AutoFilter Field:=11, Criteria1:="<>"
    End With
    ws1New.Cells.EntireColumn.AutoFit
    
    'add conditional formatting to S1 data for designating 'hot' or 'cold'
    With ws1New.Range("E4:E" & ws1New.Cells(ws1New.Rows.Count, 1).End(xlUp).Row)
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=0"
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0"
        .FormatConditions(1).Interior.ColorIndex = 41
        .FormatConditions(2).Interior.ColorIndex = 48
    End With
    
    'activate file so it's visible when routine is done
    wbTemp.Activate
    
    'rename worksheets to meaningful names
    ws1New.Name = "S1 data"
    ws2New.Name = "S2 data"
    
ErrHandler:
    On Error Resume Next
    
    'close temporary file
    wbTemp2.Close False
    
    'delete temporary file
    Kill IIf(Right(sTempP, 1) <> Application.PathSeparator, sTempP & Application.PathSeparator, sTempP) & sTempF
    
    #If DEBUGGING = False Then
        'clean up worksheets used for data collection
        wsHot.Visible = xlSheetHidden
        wsCold.Visible = xlSheetHidden
        ws1New.Visible = xlSheetHidden
        ws2New.Visible = xlSheetHidden
        wksDest.Activate
    #Else
        ws1New.Activate
    #End If
    
ExitNow:
    'turn application events back on
    Call TOGGLEEVENTS(True)
    
End Sub

Sub BorderTheArea(rngToBorder As Range)
    'sets all borders, except diagonal, in specified range
    rngToBorder.Borders(xlDiagonalDown).LineStyle = xlNone
    rngToBorder.Borders(xlDiagonalUp).LineStyle = xlNone
    rngToBorder.Borders(xlEdgeLeft).LineStyle = xlContinuous
    rngToBorder.Borders(xlEdgeLeft).ColorIndex = 0
    rngToBorder.Borders(xlEdgeLeft).Weight = xlThin
    rngToBorder.Borders(xlEdgeTop).LineStyle = xlContinuous
    rngToBorder.Borders(xlEdgeTop).ColorIndex = 0
    rngToBorder.Borders(xlEdgeTop).Weight = xlThin
    rngToBorder.Borders(xlEdgeBottom).LineStyle = xlContinuous
    rngToBorder.Borders(xlEdgeBottom).ColorIndex = 0
    rngToBorder.Borders(xlEdgeBottom).Weight = xlThin
    rngToBorder.Borders(xlEdgeRight).LineStyle = xlContinuous
    rngToBorder.Borders(xlEdgeRight).ColorIndex = 0
    rngToBorder.Borders(xlEdgeRight).Weight = xlThin
    rngToBorder.Borders(xlInsideVertical).LineStyle = xlContinuous
    rngToBorder.Borders(xlInsideVertical).ColorIndex = 0
    rngToBorder.Borders(xlInsideVertical).Weight = xlThin
    rngToBorder.Borders(xlInsideHorizontal).LineStyle = xlContinuous
    rngToBorder.Borders(xlInsideHorizontal).ColorIndex = 0
    rngToBorder.Borders(xlInsideHorizontal).Weight = xlThin
End Sub

Compare and combine Date/Times between 2 sheets of data

Sparktracer

New Member

Sparktracer

New Member

Excel Facts

Sparktracer

New Member

Sparktracer

New Member

Sparktracer

New Member

Sparktracer

New Member

Sparktracer

New Member

Zack Barresse

MrExcel MVP

Similar threads

Forum statistics

Share this page

Compare and combine Date/Times between 2 sheets of data

New Member

New Member

Excel Facts

New Member

New Member

New Member

New Member

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock