Splitting multiple row cells into new rows

[cspearsall]

Imaging - ready to work.xlsx
	A	B	C	D	E
1	Procedure Name	Modality Type	Procedure Code	CPT
2	BI BREAST CYST ASPIRATION LEFT	Breast Imaging	IMG617	19000 19000 3611900001	PR PUNCTURE ASPIRATION CYST BREAST PR PUNCTURE ASPIRATION CYST BREAST HC PUNC/ASPIR BREAST CYST
3	BI BREAST CYST ASPIRATION RIGHT	Breast Imaging	IMG1952	19000 19000 3611900001	PR PUNCTURE ASPIRATION CYST BREAST PR PUNCTURE ASPIRATION CYST BREAST HC PUNC/ASPIR BREAST CYST
4	BI BREAST DUCTOGRAM LEFT	Breast Imaging	IMG579	77053 77053 4017705301	CHG MAMMARY DUCTOGRAM OR GALACTOGRAM SINGLE CHG MAMMARY DUCTOGRAM OR GALACTOGRAM SINGLE HC MAMMARY DUCTOGRAM, SINGLE - MAMMO BREAST DUCTOGRAM
Epic - fill in (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:G1,K1,I1	Cell Value	duplicates	text	YES

I need to find a way to make each of these matching rows in columns D & E into a separate row with the same information from columns A,B&C

 

[James006]

Hi,

If I may ... Extremely Smart Decision to build a Flat Database !!!
This will make your life SO much easier ... !!!

Just out of curiosity, how many rows are you dealing with ?
By the way ... sincerely Hope you do NOT have any merged cells ...

 

[cspearsall]

@James006 - 1200-1300 rows and no merged cells

This is an extract from an EMR that we then have to cross reference with another EMR to match on CPT codes

Not sure what you mean by a flat database, from this data?

 

[James006]

Thanks for your answer ...

"Flat" means exactly what you are trying to achieve : Take Row 2, it does need to be "exploded " in 3 Rows where Column A,B,C are replicated and Columns C and D do split ...!!!

Could you check if the character which is being used as a Line Break is Alt+Enter or Char(10) ....
In cell F2, you can type =FIND(CHAR(10),E2)

 

[alansidman]

Show us an example of a mocked up solution for the sample data presented.

 

[cspearsall]

@James006 - it returns 35 so I am assuming it is Char(10)

 

[cspearsall]

@alansidman

Imaging - ready to work.xlsx
	A	B	C	D	E
1	Procedure Name	Modality Type	Procedure Code	CPT
2	BI BREAST CYST ASPIRATION LEFT	Breast Imaging	IMG617	19000 19000 3611900001	PR PUNCTURE ASPIRATION CYST BREAST PR PUNCTURE ASPIRATION CYST BREAST HC PUNC/ASPIR BREAST CYST
3	BI BREAST CYST ASPIRATION RIGHT	Breast Imaging	IMG1952	19000 19000 3611900001	PR PUNCTURE ASPIRATION CYST BREAST PR PUNCTURE ASPIRATION CYST BREAST HC PUNC/ASPIR BREAST CYST
Epic - fill in (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:G1,K1,I1	Cell Value	duplicates	text	YES

Desired result

Imaging - ready to work.xlsx
	A	B	C	D	E
1	Procedure Name	Modality Type	Procedure Code	CPT
2	BI BREAST CYST ASPIRATION LEFT	Breast Imaging	IMG617	19000	PR PUNCTURE ASPIRATION CYST BREAST
3	BI BREAST CYST ASPIRATION LEFT	Breast Imaging	IMG617	19000	PR PUNCTURE ASPIRATION CYST BREAST
4	BI BREAST CYST ASPIRATION LEFT	Breast Imaging	IMG617	3611900001	HC PUNC/ASPIR BREAST CYST
5	BI BREAST CYST ASPIRATION RIGHT	Breast Imaging	IMG1952	19000	PR PUNCTURE ASPIRATION CYST BREAST
6	BI BREAST CYST ASPIRATION RIGHT	Breast Imaging	IMG1952	19000	PR PUNCTURE ASPIRATION CYST BREAST
7	BI BREAST CYST ASPIRATION RIGHT	Breast Imaging	IMG1952	3611900001	HC PUNC/ASPIR BREAST CYST
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:E1	Cell Value	duplicates	text	YES

 

[cspearsall]

Also completely duplicate lines can be eliminated but I can do that later. I do not have access to re-export in a more favorable format

 

[James006]

it returns 35 so I am assuming it is Char(10)

Excellent News !!!

 

[James006]

If Alan come up with the adequate design for you ... a tailor-made Power Query would be ideal ... !!!

@alansidman The OP needs rows in Columns A,B,C to be replicated twice and Columns D and E split by character chr(10) ... in order for each original row to end up in 3 independent rows ... and a beautiful flat database allowing all sorts of Analysis ...