Maximum date from range of acno vba

[vivek pare]

Acno , date ,dr ,cr bal
100,12.01.2021 1000 , 1245.50
100 12.03.2021 1000 2245.50
100 12.07.2921 1000 3345.50
101
I am having acno records with transaction in each acno I want to write a flag as true if maximum date found in acno range as in 100 it is 12.07.21 like wise in another range of 101,102 1000 rows of accounts
Sir vba help
Vivek pare

 

[AlanSidman]

With Power Query, attached you can review a solution that provides the requested results. Look at the merge tab and then review the PQ steps by clicking on Queries and Connections on the Data Tab

 

[vivek pare]

kindly look the attached excel sheet vba macro to read account number range and found maximum date and put flag as true to extract data manually

 

[vivek pare]

sir
the result are printed in account nos but they are printed as below

​		​			​
21435110000003​		21435110000004​			​
​		​			​
​		​			​
​		​			​
​		​			​
​		​		​	​
​		​		​	​
​		​		​	​

 

[AlanSidman]

Please explain how the value in the Max Date column is arrived. What is the criteria for your expected result.

 

[vivek pare]

Dear sir
I just want true in column where maximum date found
Thanks

 

[vivek pare]

Sir acno 5 is printed in front of acno 4

 

[AlanSidman]

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​
1​	AC No	Table1 (2).Date	Table1 (2).Dr	Table1 (2).Cr	Table1 (2).Bal	TRUE
2​	100​	12/1/2021​	1000​	0​	1245.5​
3​	100​	12/3/2021​	1000​	0​	2245.5​
4​	100​	12/7/2021​	1000​	0​	3245.5​	TRUE
5​	101​	12/1/2021​	100​	0​	500​
6​	101​	12/3/2021​	2500​	0​	3000​
7​	101​	12/5/2021​	600​	0​	3600​	TRUE

Sheet: Merge1

 

[AlanSidman]

Did you look at solution in Post #3 which I have extracted for you in Post #12 the results. Isn't this what you are looking for? Yet you have not commented on it at all.

 

[vivek pare]

Yes sir।this is the exact result I want

 

[vivek pare]

No I want vba to automate it

 

[AlanSidman]

If you use Power Query, it automatically updates when you add or delete to your source table.

Read here about Power Query

 

[vivek pare]

Sir please give me a vba to solve it
I want to understand logic also by debugging

 

[vivek pare]

The tutorial was useful I watched it completely

 

[vivek pare]

vivek pare
About Your I just want true in column where maximum date found
... hmm? ... that would overwrite that date ... hmm?
H-column has TRUE, if there is valid maximum date.
... If not, then those ACNOs will stay in J-column.
Your #10 ... I've to skip.

Yes sir thank you it is the exact result I want sir thank you so much

 

[vivek pare]

I am not getting the code
Xacno=acno
Count same ac no and move date into dynamic array
Then get maximum date from subscript and put true
Initialise all variables
And start with new subsets of acno
In loop
Sir this is in my mind but I can not materialize
Therefore I need help
Kindly help me
Vivek pare

 

[vivek pare]

Maxifs function not supported by excel

 

[vivek pare]

Any alternative

 

[Marc L]

Maxifs function not supported by excel

Hi,​

​

I suppose last vletm code may not work on your side but as you forgot to mention which Excel version you use and on which platform (OS) …​

​

As a reminder important informations must be in the initial post with an explanation at the level of any Excel forum expects for :​

complete, crystal clear and with an attachment well reflecting the before state and the expected result,​

all the necessay in order there is nothing to guess as after all, any Excel forum is not some mind readers forum !​

​

 

[vivek pare]

Moderator note: You should use capital letter as those should use - now, You're shouting.

SIR OFFICE 2010
RUN TIME ERROR 438 OCCURED RUMMING ANOTHER CODE WITH SORT AT BELOW LINE WHILE DEBUGGING WITH F8 KEY
>>> use code - tags <<<

.SortFields.Add2 Key:=Range("A2:A" & a_max), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

 

[Marc L]

As I guessed …​

​

and put flag as true to extract data manually

What that means ?! What is the final purpose ? What is the expected final result ?​

​

 

[vivek pare]

>>> as written above <<<
>>> use code - tags <<<
object does not support error 438 this property or method occured
not running

 

[vivek pare]

Error while running the code