Converting data to another format

[Lasantha]

Dear Team,

I need your help to convert to attached data file to another format.

Kindly look in to the attached template.

• Sheet-"Data File" contains data i have. i want to convert them to as i mention in "final temp" .
• I have mentioned few examples in that sheet.("final temp").

kindly get back to me if need further details.

Kindly help me to complete this task.

Thank you,
Lsaantha.

 

[bosco_yip]

Try,

1] In A2, array formula (SHIFT+CTRL+ENTER), copied down :

=IFERROR(INDEX('Data File'!A$2:A$38,SMALL(IF(FREQUENCY(IF('Data File'!A$2:A$38<>"",MATCH('Data File'!A$2:A$38&'Data File'!B$2:B$38,'Data File'!A$2:A$38&'Data File'!B$2:B$38,0)),ROW($A$2:$A$38)-ROW($A$2)+1),ROW($A$2:$A$38)-ROW($A$2)+1),ROWS($1:1))),"")

2] In B2, array formula (SHIFT+CTRL+ENTER), copied down :

=IFERROR(INDEX('Data File'!B$2:B$38,SMALL(IF(FREQUENCY(IF('Data File'!B$2:B$38<>"",MATCH('Data File'!B$2:B$38&'Data File'!A$2:A$38,'Data File'!B$2:B$38&'Data File'!A$2:A$38,0)),ROW($A$2:$A$38)-ROW($A$2)+1),ROW($A$2:$A$38)-ROW($A$2)+1),ROWS($1:1))),"")

3] In C2, formula copied across to K2 and all copied down :

=IFERROR(INDEX('Data File'!$C$2:$E$38,AGGREGATE(15,6,ROW('Data File'!$C$2:$C$38)-ROW('Data File'!$C$1)/('Data File'!$A$2:$A$38=$A2)/('Data File'!$B$2:$B$38=$B2),RIGHT(C$1)),MATCH(LEFT(C$1,LEN(C$1)-1),'Data File'!$C$1:$E$1,0)),"")

Regards
Bosco

 

[Lasantha]

Thank you very much Bosco,
I will try this and let you know.

thank you again.
Lasantha.

 

[Lasantha]

Hi Bosco,

Could you please let me know how to copy this formula if i have more than 100 records.

thank you
Lasantha.

 

[bosco_yip]

Hi Bosco,

Could you please let me know how to copy this formula if i have more than 100 records.

thank you
Lasantha.

Hi,

1] Change the end row number from 38 to 400.

2] Provide enough column headers for the belows formulae copies across until blank.

3] See attached revise file.

Regards
Bosco

 

[Lasantha]

Thank you

 

[Lasantha]

Dear Bosco,

Hope you are doing well.
My client is requesting another modification to this worksheet.
could you please help me to do this as well.

• Kindly look into the attached template. This time they need only 6 columns by chartNumber and DeteOfServices. Please look into the examples in attached template.
• Data file-chart 1228 has 4 raws for 9/21/2017, those should be present as in Final template. one line for 1st 2 raws and one line for other 2 raws. and chart of 1228, date of services 9/22 has only 2 raws in data file. those should present in 1 raw as i mention in Final temp. (Green colored example)
• Data file- chartnumber 1778 has 3 raws for 9/22 , those should display in final template as 1st 2 raws in 1 line and remaining raw in another line. Please see the Orange colored example.
• Please review blue colored example as well.
• yellow colored example. it has 1 raw, it should be display as a 1 line.

kindly review this and let me know if required further information.
It would be great if you can help me on this as well.

Thank you very much for your time.
Lasantha.

 

[bosco_yip]

Dear Bosco,
My client is requesting another modification to this worksheet.
could you please help me to do this as well..........
Lasantha.

Hi.

1] This modification need helper column, and the resulted formula similar to post #.5

2] I added 2 helper columns in "Data File" sheet Column F and G, which can be hide if you needed.

3] See attached revise file.

Regards
Bosco

 

[Lasantha]

Thank you Sir,

Kindly help me to edit this when I have more than 400 raws. I have changed the raw number of "G" column. I think there is a issue . all equals to 11228.

Highly appreciate your support.

Thank you very much for your time.
Lasantha.

 

[bosco_yip]

Thank you Sir,

Kindly help me to edit this when I have more than 400 raws. I have changed the raw number of "G" column. I think there is a issue . all equals to 11228.

Highly appreciate your support.

Thank you very much for your time.
Lasantha.

1] Both formulae in "Data File" sheet column G and "Final Temp" sheet column B, are array formula.

2] Array formula should confirm by pressing SHIFT+CTRL+ENTER 3 keystrokes together.

Regards
Bosco

 

[Lasantha]

Thank you Bosco, got it

 

[Lasantha]

Dear @bosco_yip ,

you have created above attached working file for me in 2017. Now I have requirement to add another 2 columns as shown in "Data file" sheet. Could you please edit "Final Temp" sheet to show additional data as well.

Please let me know if you need further details. thank you very much for your time.

Lasantha.