Copy data from 2 sheets, match and place the amount on anoth

[ArijitRoy]

Hello Friends,

I have attached an example Excel file. It has 3 sheets:

• Summary By Customer
• SLA Amount
• Unbilled - Non IS

All the sheets have Customer Names. Summary By Customer have columns as "SLA Amount" & "Unbilled - Non IS" and under them have 12 months. Now I have data in SLA Amount and Unbilled  - Non IS sheet, both the sheet have Customers and month wise data. Now, match the customers from both SLA Amount and Unbilled  - Non IS sheet with Summary By Customer and amounts according to the months.

Looking for Dynamic solution. As you can see, there are data till May'23 in the Sheet 2 and 3. In future I am going to have data for June then July till December'23. Then Jan '24 will start and so on.

Please let me know if you have any question.

[smoosh]

Would something like the attached work? It gives the desired output structure. You'd still need to set it up to write the desired data to the Excel sheet. 

[ScottLewis]

Attached does the read on the first sheet, some string/date manipulation to get your output field names and the write back of that part.

You'll need to replicate it to handle the second sheet input but the logic should be similar. 

Notably, you do need a third read to get the list of customers so that you can deal with all the ones that don't have data. 

You also need to sort something about prior year. Your input data has Dec of Prior, which you might want to filter out before the date parsing logic, which I didn't do because I don't know the particulars of your input.
Should be enough to get you started.

[ArijitRoy]

Hi @ScottLewis,

I can't make any changes in the Sheet1. All the data from Sheet 2 and Sheet 3 should added into the Sheet number. According to the customer name and month.

The output I am getting on your tool, missing the months from the sheet1 under SLA Amount. June_Sys till Nov_Sys is missing.

[binuacs]

@ArijitRoy Do you want to update the "Summary By Customer" for the matching customers from both the other sheets?

[ArijitRoy]

Yes @binuacs. Also, looking for Dynamic solution.

[ScottLewis]

That's what I posted, or at least I think it is, other than the need to figure out what to do about prior year. The text input forces the output to have all the columns even in the absence of data and we're reading the entirety of the input sheet so I think it should hold for new data at least. I'm not sure which sheets you're referring to with Sheet 1/2/"number." The workflow posted (and/or the one smoosh posted, they both do the work) take the data from the range  in SLA Amount and write it to the related range on the Summary by Customer tab. 

[binuacs]

@ArijitRoy question

[ArijitRoy]

Hi @binuacs,

I have changed those headers of SLA amount as Summary By Customer in my tool with the help of Dynamic Rename "DateTimeFormat(DateTimeParse([_CurrentField_],"%Y/%m/%d"),"%b Sys")".

SLA Amount currently have data till May. Next month I am going to have June also and so on. Now, I want those months data into Summary By Customer under respected customers and month, from June Sys to December Sys will remain empty because those months data yet to come in the future.

Similarly, with Unbilled - Non IS

Kindly check the last sheet for the expected output.

December 22 removed from the SLA Amount sheet

[binuacs · Accepted solution]

@ArijitRoy Updated workflow attached