combining formulas

[shamsu203]

hi.
I have a worksheet called master and another called test. The invoice no from the master account need to be transfered to at
the inv column in the test account i have a helper column in the test sheet and using if function the the inv from the master sheet is trnsfered to the testsheet. is there any way that the helper column can be eliminated as still achiving my goal having the
invoice no in serial order. I am using the aggregate funtion in the test sheet to get the invoice no in sequence order.
tks
Shamsu

[Glenn Kennedy]

A2, copied across to b and down:
=IFERROR(IFERROR(INDEX(Master!B:B,AGGREGATE(15,6,ROW(Master!$F$4:$F$14)/(Master!$E$4:$E$14=Test!$F$1),ROWS(D$2:D2))),INDEX(Master!B:B,AGGREGATE(15,6,ROW(Master!$F$4:$F$14)/(Master!$D$4:$D$14=Test!$F$1),ROWS(D$2:D2)-SUM(--(LEN($D$2:$D$11)>0))))),"")

C2:
=IFERROR(INDEX(Master!F:F,AGGREGATE(15,6,ROW(Master!$F$4:$F$14)/(Master!$D$4:$D$14=Test!$F$1),ROWS(C$2:C2)-SUM(--(LEN($D$2:$D$11)>0)))),"")

D2:
=IFERROR(INDEX(Master!F:F,AGGREGATE(15,6,ROW(Master!$F$4:$F$14)/(Master!$E$4:$E$14=Test!$F$1),ROWS(D$2:D2))),"")

[Glenn Kennedy]

or a dyanmic array (all in one cell):

=LET(V,Master!F4:F14,DD,Master!B4:C14,C,Master!E4:E14,D,Master!D4:D14,IFERROR(VSTACK(CHOOSE({1,1,0,2},FILTER(DD,C=$F$1),FILTER(V,C=$F$1)),HSTACK(FILTER(DD,D=$F$1),FILTER(V,D=$F$1))),""))

[shamsu203]

Hi Glen
Tks for ur reply. I am trying the second formula but when I copied the formula down from A1 it gave a spill error. How to fix it . I tried what I know but no luck. Pls advice
shamsu

[Glenn Kennedy]

You do not need to copy it. Delete all results. Enter the formuka. It fills ALL cells in one go. No copy/paste. Just one cell.

[shamsu203]

Hi Glen
Tks for ur prompt reply. I copied your formula into my worksheet some of which worked as required. but in some row 2 was blank
and the data was shown in row 3 and in some no data was shown, pls advice

[HansDouwe]

Please uploud (a part of) your workbook with the unexpected results.

[shamsu203]

sorry pls find attched the worksheet

[Glenn Kennedy]

OK. It was working fine, I had just forgotten to take into account what would happen if EITHER credit OR debit had no entries...

[HansDouwe]

This formula also takes into account that the DEBET or CREDIT or both columns can be empty:

Please Login or Register  to view this content.

[shamsu203]

Hi HansDouwe
TKs your formula worked fine . some workbooks has#NA on it . how to remove it
shamsu

[Glenn Kennedy]

Did you check Post 9?

[HansDouwe]

Thanks for the feedback and rep .

I can not reproduce #NA-errors with my formula even when there are no bookings at all.
I think I've tested all possibilities.
Did I overlooked something?

Please upload your workbook with the #NA-error.

[shamsu203]

Hi
Pls see the attached file. the worksheet is named saleem
Shamsu

[shamsu203]

Hi Glen
Yes I did and am using the amended formula tks

[Glenn Kennedy]

Great! Thanks for that.

[shamsu203]

HiGlen
A qucik question. Is it possible to sort the data carried to the other sheets in date order if it is not much trouble to you.
if it can be done that would be an added bonus
Shamsu

[Glenn Kennedy]

Is this what you wanted? See purple cell on both sheets. I rearranged the formula as well, so you only have to adjust the range in ONE place (red):

=LET(Z,SORT('G Journal'!A4:F14,2,1),rZ,SEQUENCE(ROWS(Z)),V,INDEX(Z,,6),DD,INDEX(Z,rZ,{2,3}),C,INDEX(Z,,5),D,INDEX(Z,,4),E,IFERROR(VSTACK(CHOOSE({1,1,0,2},FILTER(DD,C=$F$1),FILTER(V,C=$F$1)),IFERROR(HSTACK(FILTER(DD,D=$F$1),FILTER(V,D=$F$1)),"")),""),FILTER(E,INDEX(E,,1)<>"",""))

[shamsu203]

Hi Glen
just checked ur sample file. in the cash in hand worksheet the oct 24 date should come after nov 22 and not after oct 22. can it be adjusted.
tks

[Glenn Kennedy]

In your ORIGINAL request... you SEPARATED debits and credits. Debits first and then credits. Was that just a coincidence? Do you want D & C mixed up... but in ascending order???

[Glenn Kennedy]

What VERSION of O365 do you have?

[Glenn Kennedy]

No reply. My next iteration...

Please Login or Register  to view this content.

[shamsu203]

HI Glen
Sorry I did not reply earlier . I was out and just saw ur message. Using Microsoft 365. I do not mind the D& C being mixed up as long as they are in the right column. in blank sheets where there is no entry #clac message apprear.
Tks for ur time and effort
Shamsu

[shamsu203]

Hi Glen
Tks Worked perfectly VERy much appreciated
Shamsu