Automatic Number & Date Sorting Formula

[haan]

Hi,

I would like to sort the date within a category (can be number or text).

As attached xlxs file:

For example, in column A, A1:A5 we have group 801102, while A6:A11, we have group 801103. So we would group all this together. And there's date in column B for each row from B1:B11, yet we will sort column A, then column B.

Then in E2, we would like to find the earliest date in B which belong to group 801102 or 801103.

How to do that automatically with formula?

I've attached an xlxs file, can someone help me?

Merry Christmas & Thanks!

Haan

[batman1056]

=MIN(IF($A$2:$A$12=801102,$B$2:$B$12,"Pass"))

Put this in one of the cells - enter it using Ctrl+Shirt+Enter (CSE)

[Richard Buttrey]

Hi,
In E2 and entered as an array formula with Ctrl-Shift-Enter and then copied down

Please Login or Register  to view this content.

Merry Christmas

[batman1056]

I have attached a better solution - you will need to add another colomn but just hide it - this will save you lots of time

=IF(E3=TRUE,MIN(IF($A$2:$A$12=801102,$B$2:$B$12,"Pass")),"")

where E produces a true of false and tells your if the BA number is unique, need to tweek the 801102 - I will have a look later

[haan]

Guys,

Thanks for reply. I think my explanation has confused you.

The earliest date would be the earliest date within the same category of column A (BA number).

I've attached the file with some highlights, such as yellow, red, blue in respective groups
The date would be the earliest within the highlight group of 801102, 801103, etc...
But the formula for the column E should be the same for the whole column.

Any ideas?

[batman1056]

As requested - you will need to add in a new column in order to get this working.

New Column E: Formula =COUNTIF(A$1:A1,A2)=0 - this gives a TRUE if the number is the first one of a new group
Column F: Forumula =IF(E2=TRUE,MIN(IF($A$2:$A$12=A2,$B$2:$B$12,"Pass")),"")

This needs to be input by Ctrl+Shirt+Enter (CSE)

Copy the forumas down both E and F

Done

I have attached the worked example

Alternative

Do not add the new column and use this formula
{=IF(COUNTIF(A$1:A1,A2)=0,IF(E2=TRUE,MIN(IF($A$2:$A$12=A2,$B$2:$B$12,"Pass")),""),"")}

[haan]

Hi badman1056,

Thanks.
But when I press Enter only, the {} gone missing,
When i add back the {}, and CSE, it doesn't work anymore.
what happen?

Thanks & Happy New Year!

[haan]

Hi batman1056,

Can you help me on this? The attached file...
I've added the output worksheet, which need to sort the invoice # (yellow area), based in the BA number in the grey area.

Thanks.

[DonkeyOte]

I'm not sure I follow what you're doing but given you state that BA_Database sheet is sorted you can avoid the Array in Col E altogether with a basic:

Please Login or Register  to view this content.

For output you should just use a Pivot Table based off your Database sheet (use a Dynamic Named Range as source for Pivot - or given XL2007+ convert your Db sheet to a Table and use that as source)
You can do this with formulae if insistent but IMO it would be a little pointless.

[haan]

Thanks.
I've copied your formula & paste it, certain cell is not working as i highlighted in yellow.
Please help me take a look.It should be this value : "11/9/2010"

For the output, we would like to write a report, the content including the BA#, Invoice #, and amount, arranged/sorted accordingly & show in the report. Yellow part is part of the report.

Any idea how to do that?

Thanks.

[DonkeyOte]

I've copied your formula & paste it, certain cell is not working

we will sort column A, then column B.

...given you state that BA_Database sheet is sorted [A & B per above quote] you can avoid the Array

In your most recent attachment the data is not sorted in this manner and as such the suggestion won't work.

With grouped if not sorted data you could use:

Please Login or Register  to view this content.

that said I don't really understand the significance of the result (ie where it's being used)

For the output, we would like to write a report, the content including the BA#, Invoice #, and amount, arranged/sorted accordingly & show in the report

Any idea how to do that ?

Pivot Table perhaps ?
(intro. link in signature)

[haan]

Wow! the formula works! U r great! Thanks.

The output report look like that attached. Since in BA_Database the invoice number might not be arrange properly. Then we would like to sort the data as in the yellow-highlighted area for the report.

For example, in BA database, the invoice number look like below:
20928 RI
20923 RI
20925 RI
20935 RI

But we would like to sort this in Output (report):
20923 RI
20925 RI
20928 RI
20935 RI
with their respective invoice date.

Please help.

Thanks.

[haan]

I've look at the pivot intro, but we can't implement the sorting, right? and the selection at the top of the page right?

[DonkeyOte]

OK so we're talking remittance here, correct ?
In which case a Pivot is perhaps not suitable (though could be used as intermediate step pending answers to below)

What I would say at this point is this:

If you are able to sort BA_Database by Col A & B then you should.
This will make your life considerably simpler from a basic functions perspective.

Assuming the above is not reality... next questions:

How big is the BA_Database in reality ?

Are you ever returning more than 1 Remittance at any given point in time ?

[haan]

Yea, it's kind of remittance.

I hope the other colleagues will sort BA_Database by Col A & B, but most of the time there'll be mistakes. So it's better we will be able to sort that in Output.

The database is quite small, it would be 30~50 records (row) per month. Kind of small, but we would like to keep the records. So in 1~2 years, it'll be less than 1000 records.

Thanks.

[haan]

Guys,

any idea?
Thanks

[DonkeyOte]

Sorry - slipped under the radar.

My suggestions based on what you've said and your ordering requirements etc...

Please Login or Register  to view this content.

Column I can be hidden for sake of aesthetics - designed to limit repetition in calculations and allow for data to be returned in sorted manner whilst accounting for potential duplicity.

Note the Array entry requirement for I24 though (it's not a "normal" formula)