I have the following Excel file (made up numbers):
RptLOB GLR CLR
Disability 5 3
Disability 2 1
MSL 4 6
NoLOB 3 2
AE 2 5
NoLOB 3 5
and I'm trying to find a way how to code it so in a new file, I get the following: (I've already created the new workbook with the column names)
RptLOB ECMAccount Amount
Disability GLR 7 (Sum of GLR when RptLOB = Disability)
Disability CLR 4 (Sum of CLR when RptLOB = Disability)
MSL GLR 4 (so on....)
MSL CLR 6
NoLOB GLR 6
NoLOB CLR 7
AE GLR 2
AE CLR 5
So basically, however many columns I have (GLR, CLR....in actuality I have 12 columns), that's how many times each RptLOB should be mentioned in my new file (the GLR, CLR values when the RptLOB is Disability and MSL, and so on - I have 46 different LOBs but this may change so I don't want to fix the amount)
What is the best way to approach this?
Any help will be appreciated!
Bookmarks