VBA Arrange date wise rows data in single column

[nitemath2]

HI,

I want to arrange date wise rows in single column. Sample sheet attached with expected result.

Thanks

[Glenn Kennedy]

You can do this easily with a formula. DELETE all expected results, first.

=LET(A,B2:C71,B,UNIQUE(INDEX(A,,1)),C,IFERROR(DROP(REDUCE(0,B,LAMBDA(x,y,VSTACK(x,TRANSPOSE(FILTER(INDEX(A,,2),INDEX(A,,1)=y))))),1),""),HSTACK(B,C))

Format the date column as date, and the time columns as time.

[nitemath2]

Hi Glenn, formula is not working. showing error. Please suggest

[AskMeAboutExcel]

Or a much less exciting but manual method (Cell J2 and copy down/across)

=MINIFS(  $C:$C,  $B:$B,  $I2,  $E:$E, TRIM(LEFT(J$1,9)),  $C:$C,  ">"&IF(I2>1,0,I2) )

[nitemath2]

Sorry this formula is not working on my data set

[Glenn Kennedy]

What VERSION of O365 do you have. You can find this in your account page.

[nitemath2]

HI ,
I am using MS Office 2016

[Glenn Kennedy]

Version is shown here:

[AliGW]

I am using MS Office 2016

Then why does your profile say Office 365??? Please get that changed NOW.

The formula in post #3 DOES work on your dataset, so the question is: what is different about your REAL dataset compared to the sample you shared?

[Glenn Kennedy]

Why does your profile say O365????????

[nitemath2]

sorry for the inconvenience. I have corrected my version in my profile.

[AliGW]

Please answer my question in post #9.

[nitemath2]

Sample file attached with using formula in post 3. Formula is showing error.

[AliGW]

No - that's what you posted before. Please share a version showing the solution in post #3 - we need to see what errors you are getting.

EDIT: Sorry - just realised that it uses MINIFS, which you don't have.

[nitemath2]

File attached with using of formula in post 3. Please check and do the needful.

[AliGW]

Yes, sorry - you don't have MINIFS.

Having the wrong product in your profile has wasted a lot of time for you today.

Let's hope that someone will be able to give you a solution for Excel 2016. Good luck!

[AskMeAboutExcel]

For 2016, try this instead in cell J2 - and adjust the ranges as required

=MIN(IF(($B2:$B9999=$I2)*($E2:$E9999=TRIM(LEFT(J$1,9)))*($C2:$C9999>IF(I2>1,0,I2)),$C2:$C9999))

[nitemath2]

HI thanks for the Solution and it is working but when i drag it to columns it dont stop stop showing values. Example : IF we I continue drag it from cell J2 (where date is 1st May) than it is showing till Nth column, rather showing till the date is 1st May. Any possibilities to correct it.

[AliGW]

I think this will need array entering.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[AskMeAboutExcel]

Are you entering this as an array formula as explained by AliGW? If not, follow this and see if it works.

Otherwise can you post a screenshot of what you are seeing, as I don't follow the description and can't replicate the issue with a different version of excel.

[nitemath2]

Yes, I exactly followed that. please see the attached file screen shot , where, after a zero it shows same value as showing in starting. I need to remove it through formula not manual

[Glenn Kennedy]

You didn't really ANSWER my Q about WHY your profile showed O365, when you are ACTUALLY using a much older product...

I2, copied down:
=IFERROR(INDEX($B$2:$B$71,MATCH(1,INDEX(--ISNA(MATCH($B$2:$B$71,I$1:I1,)),),)),"")

J2, copied across and down.
=IFERROR(INDEX($C:$C,AGGREGATE(15,6,ROW($C$2:$C$71)/($B$2:$B$71=$I2),COLUMNS($J2:J2))),"")

Both formula need only ENTER...

[AskMeAboutExcel]

ah, I see. A bit lengthy, but you can try something like this (entered with control+shift+enter)

=IF(  OR(  I2="-",  I2=MAX(IF($B$2:$B$9999=$I2,$C$2:$C$9999))  ),  "-",  MIN( IF( ($B2:$B9999=$I2) * ($E2:$E9999=TRIM(LEFT(J$1,9))) * ($C2:$C9999 > IF( I2>1,0,I2) ), $C2:$C9999)  )  )

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.