Complex formula to report dates

**Jackgreen1954** · 05-13-2013, 02:45 PM

Hi,

I have the following issue:
I have a tab with 2 columns:

Company Date
Company 1 13/04/13
Company 1 13/04/13
Company 1 13/04/13
Company 1 14/04/13
Company 1 14/04/13
Company 1 15/04/13
Company 1 15/04/13
Company 2 13/04/13
Company 2 13/04/13
Company 2 14/04/13
Company 2 14/04/13
Company 2 15/04/13
Company 3 13/04/13
Company 3 14/04/13
Company 3 14/04/13
Company 3 15/04/13
...

and I would like to report the dates in another tab with a formula:

Expected result:

Company Date
Company 1 13/04/13
Company 1 14/04/13
Company 1 15/04/13
Company 2 13/04/13
Company 2 14/04/13
Company 2 15/04/13
Company 3 13/04/13
Company 3 14/04/13
Company 3 15/04/13

Can anyone help me with this?

Many thanks.

Jack Green

**oeldere** · 05-13-2013, 03:06 PM

Since you work with excel 2003, I used several helpcolumns to solve your problem.

I bet there are other solutions.

See the attached file.

**ChemistB** · 05-13-2013, 03:12 PM

You can use Advanced Filter in Excel 2003

1.Select the data including column titles above rows or records that you want to sort.

2.On the Data menu, point to Filter, and then click Advanced Filter. If you are prompted as follows, click OK.
"No headers detected. Assume top row of selection is header row? "

3.Under Action, click Copy to another location.

4.Click to select the Unique records only check box.

5.In the List range box, type or select the range of records (such as A3:H10).

6.In the Criteria range box, type or select the same range of records (that is, A3:H10)

7.In the Copy to box, type the address of or select the first cell where you want the information to be placed. Make sure there is enough room so you don't overwrite pre-existing data.
This will only copy to the same sheet (choose an unoccupied area like U1). Then you can cut and paste on a new sheet.
Is this acceptable?

**Jackgreen1954** · 05-13-2013, 03:28 PM

Thanks for your quick reply.

Would it be possible to do this with just one formula?
I can have access to excel 2007 if necessary.

**oeldere** · 05-13-2013, 03:34 PM

If you have excel 2007 you can copy all date to sheet 2 and use remove duplicates.

But what are your trying to achieve?

What you gonna do with that info?

**Jackgreen1954** · 05-13-2013, 03:51 PM

What I want to do is to make sure that every time a new line is added to my first tab, the second tab (expected result) automatically update. Is it then possible to do this with a formula?

Thanks.

Jack

**oeldere** · 05-13-2013, 04:05 PM

And after that, what you gonna do with that information?

count values, compare date, insert colors etc?

**ChemistB** · 05-13-2013, 04:09 PM

You can copy oeldere's helper column formula's all the way down the page and then hide those columns and never have to worry about them again.

**Jackgreen1954** · 05-13-2013, 04:26 PM

What I want to do afterwards is to add some other columns (sales, orders, ...) that are also in the initial tab.
Expected result:
Company Date Sales Orders
Company 1 13/04/13 $7000 55
Company 1 14/04/13 $5000 20
Company 1 15/04/13 $4500 10
Company 2 13/04/13 $4600 30
Company 2 14/04/13 $6500 12
Company 2 15/04/13 $5500 20
Company 3 13/04/13 $5500 30
Company 3 14/04/13 $8500 40
Company 3 15/04/13 $7500 45

I know how to do it once the dates are reported as the example above.
I rather not hide the helper columns knowing that I have to provide the file to my client.

Jack

**oeldere** · 05-13-2013, 04:30 PM

So why not filter the data and use that data (e.g. with PDF) to send.

Or why not making a pivot table of it?

**Jackgreen1954** · 05-13-2013, 04:40 PM

The pivot table is a good idea. Unfortunately I do not think that it updates automatically if a new line is added to the initial tab.
I have to send an excel file because my client wants to aggregate it with some other data.

That is the reason why I believe that a formula is the best option.

**oeldere** · 05-13-2013, 04:42 PM

Make an table of your data, then new data will automatic be in the pivot table (if you refresh it).

I'm not sure about 2003, but in 2007 version you can do it that way.

**Jackgreen1954** · 05-13-2013, 06:36 PM

I wanted to avoid having to refresh the pivot table manually.
Are you sure there is no formula that would allow me to report the dates as in my previous example?

Jack

**oeldere** · 05-13-2013, 06:41 PM

I provided an formula (solution) in #2.

Chemist B gives an addition to my solution in #8.

I don't stated that there's no formula solution.

I personaly would go for the pivot table solution; but that's the way I like it.

Your personal solution could (of course) be different.