Monthly Alphalist of Payees

[archvanarl]

Hi! I've been trying to create an automated Monthly alphalist of payees that will generate data depending on the return period selected (map sheet in H10) taken from the INPUTS sheet. The Data on the input sheet will be loaded with transactions from january 2018 to december 2019. MAP sheet should only summarize/forward data to the MAP sheet according to the desired month and year chosen in H10.

I have attached the excel file and encoded the desired result for the month of December that i am trying to realize on the MAP sheet. The data/values should be changed depending on the return period selected from 01/2018 to 12/2019. I hope someone could help me this. I have deleted some columns and sheets to reduce the file size for me to upload the file. I will just have to readd those sheets if this will be solved. Hopefully. This could be a big help for me to avoid errors in recording and reduce workload. thank you.

[alansidman]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[archvanarl]

Oh i found a way to upload the file. but my excel file exceeds 1000mb. later.

[alansidman]

crossposted: https://www.mrexcel.com/forum/excel-...ml#post5182524

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future

[archvanarl]

Thank you very much... I have been trying to reduce the size of the excel file at the moment. I hope I could have it done today...

[archvanarl]

bump for this thread. still hoping.

[archvanarl]

up up up for the solution.

[AliGW]

You have now bumped this thread three times in the space of three hours - don't bump it again today, please.

Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

[Glenn Kennedy]

Use formulae like this one:

=IFERROR(INDEX(INPUTS!D:D,AGGREGATE(15,6,ROW(INPUTS!$A$5:$A$300)/((INPUTS!$A$5:$A$300> =$H$10)*(INPUTS!$A$5:$A$300< EDATE($H$10,1))),ROWS(B$13:B13))),"")

I have it done for columns B to G. I stopped at that point as "ATC" is not visible. It may be in one of your many hidden columns. However, all you need to do yourself is adjust the bit in red.

[archvanarl]

Oh Thank you for the idea. Partially, you got the idea that I wanted but I can only choose one between the tax base for wte and tax base for gov Mpay as well as I can only choose one between the Tax amount of WTE and Gov Mpay. But with your given formula I will try to make a separate sheets for each of those and then try to make another sheet that will merge the two tables into one..

Thank you. I am really not good at it but I'll try. I may need your assistance i I cannot make it.

[alansidman]

Here is a vba solution for you

Please Login or Register  to view this content.

[archvanarl]

Hey there, can you help me with hiding the rows when there is a zero value in the MAP sheet from column L13:L62 and unhide when the value changes from zero?

[archvanarl]

bump for this thread.

[Glenn Kennedy]

Bump for formula or vba?

[archvanarl]

Bump for formula or vba?

if formula can't make it then a vba would do. Thanks sir.

[Glenn Kennedy]

Back at my PC... but I do not understand your question...

If you select 01/2018 everything is blank. So where is the problem??

[alansidman]

I have amended my code to reflect your request

Please Login or Register  to view this content.

[archvanarl]

Hey there, I appreciate your help but I am not really good at this especially in VBA. I pasted the codes in module but whenever i ran the code there is an error "Variable not found".

[archvanarl]

Back at my PC... but I do not understand your question...

If you select 01/2018 everything is blank. So where is the problem??

Hi, Yes it is blank as the INPUT sheet is not yet loaded with transaction for the said month.

[Glenn Kennedy]

So... I do not understand what your problem is. If the VBA has not addressed it please explain and post a sheet SHOWING what you mean.

[alansidman]

When you run the code, click on Debug when you get the error and tell me which line of code is highlighted. When I ran it on your sample workbook, it ran as expected. Cannot diagnose with out this information.

[archvanarl]

This is what happened.
2018-11-27.png
2018-11-27 (1).png

I dunno if I am doing it right, but it highlights the hidden word. There is no debug option in the pop up error. Sorry for the trouble guys..

[archvanarl]

So... I do not understand what your problem is. If the VBA has not addressed it please explain and post a sheet SHOWING what you mean.

Kindly check. Thank you for the help sir.

[alansidman]

Change that line of code to

Please Login or Register  to view this content.

I have tested and it works until it hits your merged cells. You need to make sure that there are no merged cells. VBA does not like merged cells. They cause more issues than they assist. If you need to, use center across instead of merged cells. Excel, in my opinion should remove all options to merge cells.

[Glenn Kennedy]

Youer explanation was:

"When the cell in column L13 to L62, rows 13 14 and 16 should be hidden."

Isn't there something missing... like

When the cell in column L13 to L62, IS zero, then rows 13 14 and 16 should be hidden.

Exactly what do you want? If you cannot explain clearly, I cannot help.

[archvanarl]

When any of the cell in MAP sheet from column L13 to L62 containing zero, rows that falls on that cell will be automatically hidden,but when those cells changed and contains value other than zero, it will be unhidden.

[archvanarl]

Change that line of code to

Please Login or Register  to view this content.

I have tested and it works until it hits your merged cells. You need to make sure that there are no merged cells. VBA does not like merged cells. They cause more issues than they assist. If you need to, use center across instead of merged cells. Excel, in my opinion should remove all options to merge cells.

I am unsure if I understand what you are trying to explain but I will try to unmerge any cell on that sheet. Thank you. I will check it.

[Glenn Kennedy]

Finally... a clear explanation!!!

=IFERROR(INDEX(INPUTS!D:D,AGGREGATE(15,6,ROW(INPUTS!$A$5:$A$300)/((INPUTS!$A$5:$A$300> =$H$10)*(INPUTS!$A$5:$A$300< EDATE($H$10,1))*(INPUTS!$BE$5:$BE$300<> 0)),ROWS(L$13:L13))),"")

[archvanarl]

OMG you got it! Thank you very much!!! I prefer formulas than codes since my knowledge in excel is so primitive.. I may come back for any follow up question since I made an adjustment to my worksheet.. Mwuaah!!

[alansidman]

Rows 68 and 69 contain merged cells and when the code is looking to the last row, it finds these merged cells. When this happens the code fails as VBA does not work with merged cells when copying and pasting.

You may want to read these links.
https://www.reddit.com/r/excel/comme...ged_cells_bad/

https://www.quora.com/Why-are-people...merge-in-excel

[archvanarl]

Finally... a clear explanation!!!
...

One last problem. It is good when making a MAP report on tax wittheld on WTE only from INPUTS column BE and making a separate sheet for tax withheld on Gov Mpay from INPUTS column BF. I made a separate sheet that would merge the two data into one column using this formula:
=INDEX(DUE2BIR,1+INT((ROW(A1)-1)/COLUMNS(DUE2BIR)),MOD(ROW(A1)-1+COLUMNS(DUE2BIR),COLUMNS(DUE2BIR))+1)

Using the formula above, could you make a way to hide same conditions (hide rows of cells containing zero values).

[Glenn Kennedy]

Unless you have a VERY good reason for what you are doing... the approach you are taking is a bit mad!!

1. Formula for TIN on Inputs changed to remove errors, Id5, copied across and down:
=IFERROR(VLOOKUP($C5,'!'!$N$3:$S$100,COLUMNS($A:C),FALSE),"")

2. Crazy column duplication removed from Map_Merged.

3. Complete re-write of all columns.

There is now 1 line per company in each period searched. The Tax base is grossed for all matching lines (company name and TIN - this may be excessive and can be streamlined to just the company name...) and tax withheld is also grossed in the same way.

All ranges on Map_merged run down 300 rows. You no longer need sheet Map (I think...).

[archvanarl]

I am very sorry. I have been trying to have have both tax withheld in wte and gov mpay listed into one column but not to sum it. The sheet you were working will still be useful in submitting separate reports. Thank you very much and sorry for the trouble.

[Glenn Kennedy]

What EXACTLY do you mean by:

I have been trying to have have both tax withheld in wte and gov mpay listed into one column but not to sum it.

Show me on a sheet EXACTLY how you want it to look.

[archvanarl]

I have re-uploaded the file with comments on each sheet. I hope I will be able to express myself. I am very grateful to have you sir.

[AliGW]

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[Glenn Kennedy]

Another iteration. Still using Map_Merged. Companies will appear grouped by name. So, in this sheet there are now two enties for Laneco, both with MPAY and WTE. The Gov MPAY's appear first (along with the corresponding tax base amount and payment nature (cols J and I). Then the WTE's appear.

See what you think...

[archvanarl]

You really got what I wanted, but the payment nature and the amount tax base only shows data for GovMpay only and blank for the wte.

[Glenn Kennedy]

I'll look at this in UK morning.

[archvanarl]

Thank you. I am looking forward to have it. Badly needed. Take your time and thank you once again.

[Glenn Kennedy]

Another go...

[archvanarl]

From your given formulae, I have edited some range in inputs from 300 to 1000 and some Column letters to match the Tax base and the tax withheld in the MAP_merged cell as well as some variables. Now, the only problem remains is in the MAP_merged (Nature of Payment), the data for Tax withheld for WTE is blank. I hope you can forgive me in this one.

[archvanarl]

Bump of this thread....

[archvanarl]

UP! hmmm. somebody help.

[Glenn Kennedy]

I've been spending some time in the Real World...

One way to fix that is by the use of a helper column (which can be hidden, at column M of Map_Merged