filter data from old date to last date based on duplicates items

[Mussa]

Hello
I have duplicates items in column B . what I want filter data from old date to last date based on date in column A for each duplicates item in column B
the date will be MM/DD/YYYY
the original data

df.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	CODE	INV-N.O	CLIENT-N.O	BRAND	TYPE	ORGIN	QTY	PRICE	TOTAL
2	04/01/2023	A02	BlS1/12-021	CC-MMSN1/12	AC-AD1	AA-100	TTY1	10	140	1400
3	04/05/2021	A03	BlS1/12-022	CC-MMSN1/13	AC-AD2	AA-101	TTY2	12	200	2400
4	04/23/2021	A04	BlS1/12-023	CC-MMSN1/14	AC-AD3	AA-102	TTY3	16	130	2080
5	04/04/2021	A02	BlS1/12-024	CC-MMSN1/15	AC-AD1	AA-100	TTY1	10	146	1460
6	06/14/2021	A03	BlS1/12-025	CC-MMSN1/16	AC-AD2	AA-101	TTY2	12	200	2400
7	04/13/2021	A02	BlS1/12-026	CC-MMSN1/17	AC-AD3	AA-102	TTY3	16	144	2304
sh1

Cell Formulas
Range	Formula
J2:J7	J2	=I2*H2

what I want it

df.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	CODE	INV-N.O	CLIENT-N.O	BRAND	TYPE	ORGIN	QTY	PRICE	TOTAL
2	04/04/2021	A02	BlS1/12-024	CC-MMSN1/15	AC-AD1	AA-100	TTY1	10	146	1460
3	04/13/2021	A02	BlS1/12-026	CC-MMSN1/17	AC-AD3	AA-102	TTY3	16	144	2304
4	04/01/2023	A02	BlS1/12-021	CC-MMSN1/12	AC-AD1	AA-100	TTY1	10	140	1400
5	04/05/2021	A03	BlS1/12-022	CC-MMSN1/13	AC-AD2	AA-101	TTY2	12	200	2400
6	06/14/2021	A03	BlS1/12-025	CC-MMSN1/16	AC-AD2	AA-101	TTY2	12	200	2400
7	04/23/2021	A04	BlS1/12-023	CC-MMSN1/14	AC-AD3	AA-102	TTY3	16	130	2080
sh1

Cell Formulas
Range	Formula
J2:J7	J2	=I2*H2

my real data about 6700 rows .
thanks

 

[awoohaw]

To sort data as it is, just use the AUTOFILTER found on the DATA ribbon.

If you want to have a filtered report, you can use the Advanced Filter also on the DATA ribbon.

If you want to filter with a formula, you could try this:
(This requires you to allocate enough rows to accomodate the maximum number of occurances of the look up value).

Mr excel questions 47.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	CODE	INV-N.O	CLIENT-N.O	BRAND	TYPE	ORGIN	QTY	PRICE	TOTAL
2	Wednesday, January 4, 2023	A02	BlS1/12-021	CC-MMSN1/12	AC-AD1	AA-100	TTY1	10	140	1400
3	Tuesday, May 4, 2021	A03	BlS1/12-022	CC-MMSN1/13	AC-AD2	AA-101	TTY2	12	200	2400
4	Friday, April 23, 2021	A04	BlS1/12-023	CC-MMSN1/14	AC-AD3	AA-102	TTY3	16	130	2080
5	Sunday, April 4, 2021	A02	BlS1/12-024	CC-MMSN1/15	AC-AD1	AA-100	TTY1	10	146	1460
6	Monday, June 14, 2021	A03	BlS1/12-025	CC-MMSN1/16	AC-AD2	AA-101	TTY2	12	200	2400
7	Tuesday, April 13, 2021	A02	BlS1/12-026	CC-MMSN1/17	AC-AD3	AA-102	TTY3	16	144	2304
8
9
10
11
12	CODE	A02
13	Sunday, April 4, 2021	A02	BlS1/12-024	CC-MMSN1/15	AC-AD1	AA-100	TTY1	10	146	1460
14	Tuesday, April 13, 2021	A02	BlS1/12-026	CC-MMSN1/17	AC-AD3	AA-102	TTY3	16	144	2304
15	Wednesday, January 4, 2023	A02	BlS1/12-021	CC-MMSN1/12	AC-AD1	AA-100	TTY1	10	140	1400
mussa

Cell Formulas
Range	Formula
J2:J7	J2	=I2*H2
A13:A15	A13	=IF(COUNTIF($B$2:$B$7,$B$12)<ROWS($A$13:A13),"",1/(LARGE(1/($A$2:$A$7)*(--($B$12=$B$2:$B$7)),ROWS($A$13:A13))))
B13:J15	B13	=INDEX($B$2:$J$7,MATCH($A13&$B$12,$A$2:$A$7&$B$2:$B$7,0),0)
Dynamic array formulas.

 

[Mussa]

thanks, but as I said

my real data about 6700 rows .

I don't only autofilter couldn't succeed . I think need more than of that like array with autofilter by vba

 

[awoohaw]

Okay, I understand your requirement better. But tell me... why do you want to have to page through 6700 or more records to find the group of CODES that you want to have as your primary sort? You'd be paging down or using the find function quite a bit which is time consuming.

The advanced filter or the formula I suggested may be a more efficient solution for your question. If you are filtering "raw" data I think it is messing with fire as you can corrupt your source data with an inadvertant (or improperly selected) sort.

If you have access to Excel 365 or Excel 2021(Maybe) you would be able to use the FILTER function which is a great new addition.

Best Wishes.

 

[Mussa]

why do you want to have to page through 6700 or more records to find the group of CODES that you want to have as your primary sort?

I have macro to populate all records 6700 rows from all of sheets and put in one sheet as in OP when populate data will not arrange duplicates items under each other so I search for macro to do that from old date to last date .

You'd be paging down or using the find function quite a bit which is time consuming.

I have another macro to search item without paging down.

If you are filtering "raw" data I think it is messing with fire as you can corrupt your source data with an inadvertant (or improperly selected) sort.

this is the first time body doesn't advice use filter data!

If you have access to Excel 365 or Excel 2021(Maybe) you would be able to use the FILTER function which is a great new addition.

based on my account detail of course not .

or the formula I suggested may be a more efficient solution for your question

obviously vba is not available so far, so I will test your formula and see how goes.

 

[awoohaw]

If you are filtering "raw" data I think it is messing with fire as you can corrupt your source data with an inadvertant (or improperly selected) sort.

this is the first time body doesn't advice use filter data!

I think you misunderstood. Or least I hope you did.

With autofilter and/or some data models it may not be such a problem. But, it seems you aren't using those from your comments.


If you have COLUMNS A:ZZ and you want to sort or filter based on something in column MM and you do not select the entire range A##:ZZ## then you will be misaligning your rows. AND if that is RAW DATA (the only true and factual source of your information) you will permanently and utterly destroy your company's work if you have no back up.

I encourage all excel users I work with to NEVER EVER filter or sort RAW DATA worksheets/ranges/tables.


Please let me know the results of the formula I suggested. I am well aware it deviates from your desired process. If it doesn't work, I wish you the best of luck in finding a solution here. Mr. Excel is a great resource and I'm sure there are folks that have a great solution for you. And some that may even disagree with what I wrote above.

 

[Mussa]

doesn't see to be work the formula, it shows error #VALUE!

 

[awoohaw]

doesn't see to be work the formula, it shows error #VALUE!

Try committing the formula with the CNTL-SHFT-ENTR key stroke.

If that doesn't work then you'll need to figure a way to send a mini worksheet. As you can see it works for me.

 

[Mussa]

thanks , what about the rest of the data?

 

[awoohaw]

thanks , what about the rest of the data?

what do you mean?


Did you enter the formula with CNTL-SHFT-ENTR keystroke? Did you copy it down your columns?

Again, if you still get an error you need to figure out how you can get us what exact formula you used and what exact cell references and contents you used.

we can't debug "I get a #VALUE error".