Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have data in my spreadsheet in a worksheet like this:

1

Where the data goes in this format

[ID] | [Date] | [Hour] |

And I have another worksheet where I want the data to be like this:

2

Where I want the data displayed to be like:

[DateRange's First ID + " - " + DateRange's Last ID] | [DateRange's First ID + " - " + DateRange's Last ID] | [Date]

How could I make this using formulas or VBA macros?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

If you don't mind using PivotTable a bit...

I would run a PivotTable onto the data, put Date as row labels, and two ID in Values. The first ID will be summarized by minimum, and the second by maximum.

enter image description here

Then, you can Copy/Paste (Paste values and format the date) the information you need into a new sheet, where you will move the date column to the right. Insert two columns between the Max ID and the Date, then do a simple concatenate:

=CONCATENATE(A2," - ",B2)

(I forgot the spaces in my screenshot below)

enter image description here

This formula also works: =A2&" - "&B2

You only need to rename the column headings and after removing the formulae in the cells through Copy/Paste Values, you can delete columns A and B.

share|improve this answer
    
Thank you, it works like a charm :) –  JhonAlx May 25 '13 at 13:34
    
@JhonAlx You're welcome :) –  Jerry May 25 '13 at 13:35
    
Just have to do some copy/pasting but it saves me a whole lotta time :D –  JhonAlx May 25 '13 at 13:44

If you want the first and last ID, try

=INDEX(Sheet1!A:A,MATCH(C1,Sheet1!B:B,0))&" - "&INDEX(Sheet1!A:A,MATCH(C1,Sheet1!B:B,1))

where C1 on the current sheet has the date.

If the IDs are not in ascending order and you want the lowest ID and the highest ID for the date, try

=MIN(INDEX(Sheet1!A:A,MATCH(C1,Sheet1!B:B,0)):INDEX(Sheet1!A:A,MATCH(C1,Sheet1!B:B,1)))&" - "&MAX(INDEX(Sheet1!A:A,MATCH(C1,Sheet1!B:B,0)):INDEX(Sheet1!A:A,MATCH(C1,Sheet1!B:B,1)))
share|improve this answer
    
+1 because it works but @Jerry solution is faster :) –  JhonAlx May 27 '13 at 20:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.