Streamlining Process for Consolidating Unique Payroll Data Lines

[Gunasekaran]

Hi Team,
I'm assisting the Payroll Team with streamlining a process. I need to consolidate multiple lines with unique values in columns A to G into a single line, computing the total sum value for these lines.

Currently, I'm manually preparing and updating these lines, but with over 1000 lines, it's becoming time-consuming. I typically use filters or combine values from columns A to G and create a pivot table to update a new sheet.

I'm seeking an automated solution to efficiently generate a set of unique lines with the total sum amount. I've provided an example for your reference. Your assistance in this matter would be greatly appreciated.

 

[Marc L]

Hi, as your attachment does not match your explanation so, according to this explanation,​

a beginner level Excel basics way which can be done under VBA as well :​

first use an additional column to join columns A to G then​

use an advanced filter to create the uniques list according to this additional column then​

use an Excel basics formula - like SUMIF for example - to compute the sums.​

​

Could be achieved without an additional column but could be slower to execute as that should need a SUMIFS formula​

depending on the Excel version used we could not guess !​

You could try as well SUMPRODUCT which should be the better way since last century whatever the Excel version …​

​

 

[p45cal]

In the attached, new results table at cell A13 of the Output sheet.
It's a Power Query query.
At the moment it needs refreshing manually to bring it up to date (a right-click on the table, then choose Refresh) but this can be automated, by say the source table changing.
I've added column K manually to show which rows in the source sheet are contributing to a single row in the results table.
This is different from your results table (which I think is wrong!). I've added some colour to the source data to show the grouping too.
It's consolidating (grouping) source data rows based on columns A:G of the Source sheet.
The data on the Source sheet has been made into a proper Excel table.
I have guessed that the Curr column will also contain numeric values and have summed them. If this is wrong then if there's text in there (does Curr stand for Currency?) it will complain. Come back and I'll tweak.

You can also do this with a Pivot Table, see result at cell A23.

 

[Marc L]

which I think is wrong!

Hello, obviously wrong, the reason why I do not waste time to try to reproduce such impossible wrong result …​

​

 

[Marc L]

does Curr stand for Currency?

In this case the columns to combine should be A to H rather than A to G !​

As guessing can't be coding …​

​

 

[Gunasekaran]

Hello everyone,

I would like to express my gratitude for your support. Currently, the Power query in the file is functioning properly. Since I only have one currency, I have omitted the column information related to it. As per your suggestion, I have shared the revised file with you.

In the actual process, the user will download the source file from ERP and paste it into the respective .xlsm file. When they run the macro, the JV information will be updated in the JV sheet accordingly. Additionally, I will create a Combine JV sheet on the next tab, where I only require the combined data from columns A to H. this is actual my Payroll process,

( But i need VBA Macro code, It would be helpful and update that code in my .xlsm file, because i am not familiar with Power query and how to update it in original file that Power Query Setup)

If the Power query is set up as a one-time setup, can we configure it in the original .xlsm file? This way, if the data in the JV sheet is deleted and pasted again, will it still work?

 

[p45cal]

the user will download the source file from ERP and paste it

It's very likely you'll be able to avoid the copy/pasting of data from such a file since Power Query is designed to extract data from such files; could we see an example of such a file?

1. Separately, the Dim3 column seems to be numbers but is it actually, like the Ledger Acc column, data that you'd never do any arithmetic on?
2. Is it OK to lose the Curr column altogether?

 

[Marc L]

paste it into the respective .xlsm file

As this file is missing within your attachments ‼ ​

​

And more for any VBA code I need at least your initial post attachment without any error in the expected result …​

​

 

[Marc L]

But i need VBA Macro code

But why ?! As you just need to 'refresh' via a right click the pivot p45cal have done​

and just using the Macro Recorder you will get the unique necessary VBA codeline …​

​

 

[Gunasekaran]

Dear Both, that JV Sheet always not Table format, whenever run clear will delete the data and User do their part, finally JV entry will ready in JV Sheet. How to power query automatically create table format of JV Range data.

if each time Create Table, power query will show the error????
if am wrong, kindly guide and give me the easy solution to avoid manual work

 

[Marc L]

As 'JV' sheet does not exist in any of your attachment so you are far than getting another solution.​

Anyway you already have the p45cal pivot way so just well reading & following my post #9 …​

​

If you have issue with pivot - could be the case between different Excel versions - then,​

once your initial post attachment corrected without any error,​

I could give it a try with the last century universal way like any beginner operating an advanced filter and applying a formula,​

could be slower than a pivot but not a concern according to your small data …​

​

 

[p45cal]

Power Query is designed to extract data from such files

With my suggestion, there'll be no need to have a JV Sheet at all. The consolidated JV data will update according to new ERP data file. No need to delete anything.

 

[Gunasekaran]

With my suggestion, there'll be no need to have a JV Sheet at all. The consolidated JV data will update according to new ERP data file. No need to delete anything.

Thanks a Lot P45call and Marc L