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

Thank you very for you time.

Lasantha.

#### Attachments

• 15.9 KB Views: 5

#### 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

• 22.4 KB Views: 6

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

Lasantha.

#### Peter Bartholomew

##### Well-Known Member
Just to demonstrate the methods at #2 actually work!
The FILTER sheet will not update without modern dynamic arrays.
The INDEX/LOOKUP should read across unless I have messed up

#### Attachments

• 48.6 KB Views: 7

#### Lasantha

##### Member
Just to demonstrate the methods at #2 actually work!
The FILTER sheet will not update without modern dynamic arrays.
The INDEX/LOOKUP should read across unless I have messed up
Hi @Peter Bartholomew ,

This is great . thank you so much .

Lasantha.