• 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.

Data Converting

Lasantha

Member
Dear All,

Kindly look into the attached template. it contains data which i have to convert (Column A to Column G). so I want to convert those details as show in right hand side. if you check column A to G it contain several different lines for 1 chartNum . but actually i need 1 charNum as shown in right hand side.

Could you please help me to complete this.

Thank you very for you time.

Lasantha.
 

Attachments

Peter Bartholomew

Well-Known Member
I wonder whether you recognise what a huge step backwards the 'new' data format is relative to the original when it comes to finding information or processing data?

Anyway, the starting point is to introduce a sequential record number and, for each chart number a sequential item number. The item number is returned by counting the number of occurrences of the chart number that are either physically above the current row or have a smaller record number (same items by slightly different COUNTIFS formula).

Once you have a set of unique keys you can lookup data using INDEX/MATCH, INDEX/LOOKUP or, with future versions of Excel, FILTER.

p.s. I would post a solution but I used FILTER. Sorry.
 

John Jairo V

Well-Known Member
Hi, to both!

You can try this formulas:
[J2] : =COUNTIF(A:A,K2) ; and drag it down. (I leave this auxiliar formulation for you can see how many blocks you will need).
[L2] : =IF(INT(1+(COLUMNS($L2:L2)-1)/7)>$J2,"",IF(MOD(COLUMNS($L2:L2),7),INDEX($B:$G,MATCH($K2,$A:$A,)+(COLUMNS($L2:L2)-1)/7,1+MOD(COLUMNS($L2:L2)-1,7)),"")) ; and drag it right and down.

See file. Blessings!
 

Attachments

Lasantha

Member
Hi, to both!

You can try this formulas:
[J2] : =COUNTIF(A:A,K2) ; and drag it down. (I leave this auxiliar formulation for you can see how many blocks you will need).
[L2] : =IF(INT(1+(COLUMNS($L2:L2)-1)/7)>$J2,"",IF(MOD(COLUMNS($L2:L2),7),INDEX($B:$G,MATCH($K2,$A:$A,)+(COLUMNS($L2:L2)-1)/7,1+MOD(COLUMNS($L2:L2)-1,7)),"")) ; and drag it right and down.

See file. Blessings!
Dear @John Jairo V

Thank you very much for the support. This is perfectly matching my requirement.

thanks for your time.

Lasantha.
 
Top