Merge like multiple records on text file import into Excel

[tech217]

I am importing 3 different text files into a worksheet.
Each record consists of a number and a timestamp.
When the number is the same(it is repeated in each file), I want to be able to merge the records and calculate elapsed time. Any suggestions are greatly appreciated!

Regards,
Mark C.

 

[SkipVought]

Mark,

I named the two ranges Num and Date

I have the list of unique numbers starting in D2
[tt]
MAX: =IF(ISNA(INDEX(Date,MATCH(D3,Num,0)-1,1)),INDEX(Date,COUNT(Date),1),INDEX(Date,MATCH(D3,Num,0)-1,1))
MIN: =INDEX(Date,MATCH($D2,Num,0),1)
[/tt]
so max-min+1 is your day span



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tech217]

This is a little over my head, please walk me thru setting this up.

Thanks

Regards,
Mark C.

 

[SkipVought]

On Naming Ranges faq68-1331 How can I rename a table as it changes size

Since there are 2 rather complex formulas, one for the MIN date and the other for the MAX date associated with a number (assuming, BTW, that the table is sorted by Number, by Date) the result that you are looking for is the difference plus one.

1) Name the Column Hadings, Num & Date

2) Select the entire table

3) Insert/Name/Create - create names in top row -- now your ranges of data in your table is named Num and Date

4) sort the table

5) gen a list of unique Nbrs using Data/Advanced Filter - Filter to a New Location, no criteria, unique values

6) using that list (mine is in COLUMN D) enter the two formulas for the first line, modify the references -- notice that the MAX formula has a reference D3,to the NEXT Number

assuming that the Number list is in Column D, the MAX formula is in Column E and the MIN formula is in Column F, then in G
[tt]
=E2-F2+1
[/tt]
is the Day Span for the number in D2



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tech217]

Thanks for your help Skip, that looks like it will work for this case. Unfortunately, I talked to the end user today and the files coming in will be different.

Regards,
Mark C.

 

[SkipVought]

Well how WILL they be formatter?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884