VB Code or formula to reorganize data

[rizmomin]

Hello:

Please refer to attached file.
I have bank statemment data as shown in column A thru G
Column D is expense, column E is Income.
I need either VB Code or formula to put this data per date as in column I.
Example:
I will have description entered in cell J1:Q1 for Income (Column E).
I see 3 deposit on 10/2/2017 for 745,762 and 833.
So these deposits need to go in cell J3,K3 and L3 as description is in cell J1:N1.
Same way need to see the TEXT in J1:Q1 and enter in per date.
Same way need to see the TEXT in T1:U1 and enter in per date for expense.

Let me know if you have any questions.
Thanks.

Riz

[Special-K]

I wont be solving this but your output table implies there will never be more than 5 deposits per day (since you only have 5 columns for deposits (J-N).
Is that correct, will there never be more than 5 deposits per day?

[rizmomin]

Hello:

Good question, may be i can extend to 10 deposits per day...as per attached.
Please let me know if you have any questions.
Thanks.

[Glenn Kennedy]

Three different formulae, as shown in the attached, variants of:

=IFERROR(INDEX($E:$E,AGGREGATE(15,6,ROW(Table1[[Credit]:[Credit]])/((Table1[[.]:[.]]=$I2)*(Table1[[.3]:[.3]]=J$1)),COUNTIF($J$1:J$1,J$1))),"")

[Glenn Kennedy]

Modified as per last post. NB. Leading space before "Gift Cards FDCLGIFT" in body of data (3 instances) were deleted.

[rizmomin]

Hello Glenn:

Great formula approach.
Thanks a lot....i will let you know if any further questions/requirement.
Riz

[Glenn Kennedy]

You're welcome, Riz.

[rizmomin]

Hello Glenn and other VB Coder:

I really liked your formula approach however since i have lots of data in as sheet, its slowing down the other calculation process.
Would be great if this can be achieved by VB Code or some way to activate the formula to calculate when needed.

Would appreciate help.

Thanks a lot

Riz

[xladept]

Hi Riz,

Try this:

Please Login or Register  to view this content.

[rizmomin]

Hello xla:

Thanks a lot, seems to work.
The data i am getting is in TEXT format.
I need to convert into Number formats.
Can you help change.
I will test more, if any other issues.
Thanks\

Riz

[xladept]

Hi Riz,

No strings

Please Login or Register  to view this content.

[rizmomin]

Hello xla:

Thanks a lot, i will implement with my data sheet and see if any problem.
Mostly like it should be ok but is there as way the Lookup you have above can be changed so it looks with text containing...
example :
GPC GPC EFT XXXXXX1012FTV , instead of looking at the whole content, if we can have text containing "GPC GPC EFT"
Gift Cards FDCLGIFT, instead of looking at the whole content, if we can have text containing "Gift Cards"

Also it will save lots of time to start the next run with the last dated entry instead of running from row2.
I hope this make sense.


Please let me know if you have any questions.
Thanks.

Riz

[xladept]

Hi Riz,

Another way:

Please Login or Register  to view this content.

[rizmomin]

Hello xla:

Would this take care of

Please Login or Register  to view this content.

[rizmomin]

Hello xladept:

Just to let you know that i can have date field in cell H2 which will be in format MMMYY.
Based on the value in cell H2, code should run from data in column A from that point.
I hope this is clear.
Please let me know if you have any questions.
Thanks.

Riz

[xladept]

Hi Riz,

I was able to key on the last deposit entry:

Please Login or Register  to view this content.

For the H2 - could you post another sample?

[rizmomin]

Hello xla:

Please refer to attached file.
1st time running macro no probem.

I have pasted more data and run macro again and it erros out at line 3 @ r = F.Row

I would really prefer to have as follows
I use the month from dropdown list in H2.
Then based on the selection, chosse the 1st day of the month from column and use the data down to bring across.
I hope this make sense.
Please let me know if you have any questions.
Thanks.

Riz

[xladept]

Try this version:

Please Login or Register  to view this content.

* It'll flake out if there is no corresponding date in Column I.

[rizmomin]

Hello xlaDept:

I am sorry for late response.
I have been sick with flu.
This should work and if any further question, will let you know.
Thanks a lot

Riz

[xladept]

Hi Riz old friend,

Sick with the flu? - I'm sorry and also curious. Did you have a flu shot?

At any rate: you're welcome and thanks for the rep!