• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Retrieve column Header based on text in the column and aging with pivot

V

Vijayarc

Member
Hi Ninjas

->we have sheets which data update daily dynamically 1500 to 2000 rows

--> attached sample excel columns text be as - (NoRelationship - 3 Days , Rejected - 125 Days , Completed - 9 Days )
--> Requirement result samples are highlighted in green
1. find Word "Rejected" in 16 Red highlight columns and retrieve respective column headers - (attached file have sample 9 red column only)
3. find Ageing Bucket of each reject( by number in between string)
4. pivot on Ageing Bucket based on LOB Names

is it possible to automate this conditions
please help to automate the case, for large row numbers takes 3 hrs for this daily
 

Attachments

  • lob reject.xlsx
    13.1 KB · Views: 4
Vijayarc
Could You use same term with Your writings and file?
Could You fill eg with pink fonts Your needed correct results from Your sample data?
 
vletm said:
Vijayarc
Could You use same term with Your writings and file?
Could You fill eg with pink fonts Your needed correct results from Your sample data?
Click to expand...

Hi Ninja
sorry for confusion,

I have attached the correct result table file done manually as request

1. find Word "Rejected" in 16 Red highlight columns and retrieve respective column headers - AQ Column
2. find Ageing Bucket of each reject( by number in between string) - AR ,AS column
3.pivot on Ageing Bucket based on LOB Names

i do no to pivot if 2 reject on same rows

is it possible to automate this conditions
please help to automate the case, for large row numbers takes 3 hrs for this daily
 

Attachments

  • LOB Reject.xlsx
    13.6 KB · Views: 1
Vijayarc
I asked two things ... You skipped #1 ... hmm?
As You saw with Your manually done values that won't work with ... Pivot.
You can find [ Do It ]-button in Cell T1 ... press it!
 

Attachments

  • LOB Reject.xlsb
    19.9 KB · Views: 6
Hello sir,
Thanks for your help!!!
sorry sir, some condition missing in code that- count of each Lob Rejects should segregates based on "G" Columns Renewal and Non Renewal as per my manual result sheet
Line of Business
Mkts - E trading​
​
Mkts-Collateral
Non Renewal​
Renewal​
Non Renewal​
Renewal​
0- 5 days
​
​
​
1​
6-15 days
​
​
​
16-30 days
​
​
1​
4​
30 + days
1​
2​
​
​
Grand Total
3​
​
5
 
vletm said:
Vijayarc
I asked two things ... You skipped #1 ... hmm?
As You saw with Your manually done values that won't work with ... Pivot.
You can find [ Do It ]-button in Cell T1 ... press it!
Click to expand...
hello sir,
Thanks for your help!!!
sorry sir, some condition missing in code that- count of each Lob Rejects should segregates based on "G" Columns Renewal and Non Renewal as per my manual result sheet
Kindly help sir, this condition is very important
Line of BusinessMkts - E tradingMkts-Collateral
Non RenewalRenewalNon RenewalRenewal
0- 5 days1
6-15 days
16-30 days14
30 + days12
Grand Total35
 
Vijayarc
As You have written TWO times:
1. find Word "Rejected" in 16 Red highlight columns and retrieve respective column headers
2. find Ageing Bucket of each reject( by number in between string) - AR ,AS column
3.pivot on Ageing Bucket based on LOB Names
How something else would be important, if You haven't written those earlier?
... and still You're skipping my questions!
 
vletm said:
Vijayarc
As You have written TWO times:
1. find Word "Rejected" in 16 Red highlight columns and retrieve respective column headers
2. find Ageing Bucket of each reject( by number in between string) - AR ,AS column
3.pivot on Ageing Bucket based on LOB Names
How something else would be important, if You haven't written those earlier?
... and still You're skipping my questions!
Click to expand...
Hi Sir

sorry, i did't understand your first question - please excuse
please help to segregates ageing bucket based on "G" Columns Renewal and Non Renewal
 
Vijayarc
I ask questions to help You!
If You don't understand question then ask more details
- if You skip any question
- that means You do not want to help Yourself.
#1 Could You use same term with Your writings and file?
Where are 'LOB Names', 'Ageing Bucket' in Your files?
You should use same texts (=terms) in both!

Did You know that even 'minor modification' could mean to start from zero?

... and now You skipped also my previous question ... hmm?
How something else would be important, if You haven't written those earlier?
 
vletm said:
Vijayarc
I ask questions to help You!
If You don't understand question then ask more details
- if You skip any question
- that means You do not want to help Yourself.
#1 Could You use same term with Your writings and file?
Where are 'LOB Names', 'Ageing Bucket' in Your files?
You should use same texts (=terms) in both!

Did You know that even 'minor modification' could mean to start from zero?

... and now You skipped also my previous question ... hmm?
How something else would be important, if You haven't written those earlier?
Click to expand...

Hi Sir

Kindly apologize for my repeated mistakes,

1.Question : 'LOB Names- 15 red color Highlighted column names are LOB(Line of Business)names
Ageing Bucket - AR ,AS column in sheet are Ageing bucket
2.Question : sorry sir,i forget to add very important 2 point missed in my fist post

Did You know that even 'minor modification' could mean to start from zero? -
Yes sir, i can understand - please apologize
please excuse this as last for wasted your time - kindly help help to segregates ageing bucket based on "G" Columns Renewal and Non Renewal
 
Vijayarc
1) 'find Word "Rejected" in 16 Red highlight columns' almost same '15 red color Highlighted column names'
2) Do You mean 'just remember' something very important?
+ Did You know ...? Seems that You didn't know. Means, You should focus.
 

Attachments

  • LOB Reject.xlsb
    22.6 KB · Views: 1
Hi sir
You should focus. --> i assure, i will be more focus in furthers i wont repeat it again

1) 'find Word "Rejected" in 16 Red highlight columns' almost same '15 red color Highlighted column names' -> only 15 red columns names
2) Do You mean 'just remember' something very important? ---> i assure, i will note important condition before posting thread

sir, once issue is ,as per below eg: if there is 2 Reject in both Renewal and Non Renewal in a LOB- the numbers should split in both Renewal and Non Renewal.
But as per code the number populates one of case.
please help to segregates ageing bucket based on Renewal and Non Renewal
Line of Business Mkts - E tradingMkts-Collateral
Non RenewalRenewalNon RenewalRenewal
0- 5 days1
6-15 days2
16-30 days14
30 + days1
Grand Total 3 6
 
Vijayarc
Did You write? i assure, i will note important condition before posting thread
Do You know what do that means?
Maybe soon You'll write that ooops
... there will be 11 different variations of 'something like Renewal'!
... and after that ... there will be own row per every day value!
... what would be next?
 
vletm said:
Vijayarc
Did You write? i assure, i will note important condition before posting thread
Do You know what do that means?
Maybe soon You'll write that ooops
... there will be 11 different variations of 'something like Renewal'!
... and after that ... there will be own row per every day value!
... what would be next?
Click to expand...
Yes sir,I mean that I will ensure all possible Condition and requirement post in thread at first
---I didn't understand mean 11 different renewal
-- I do no what to answer for every day value
-- if fixed Columns for all 15 lobs for renewal and non renewal -- 30 coluns will fixed

--- please help sir,one last time , it reduces 3 hrs for my manual work daily
 
hmm?
You've written Yes sir, i can understand
... but seems that You have not understood.
You've already add few 'just remember' 'minor things'.
... and ...
really that above version won't reduce nothing?
 
You must log in or register to reply here.
Back
Top