Rearrange data in spreadsheet to specific format

[NKUNZI]

Please assist with an automated way either formula or VBA, to remove and place order No to left of ICN no for each order under that order No, and have a sequential list without spaces.
this requires that the items under each order no needs to be identified in turn and matched to the items in the order. An example of what the sheet looks like is given below, this needs to be changed to place the EGU-ORD No next to only the lines with a cat no, ignoring lines that have other data, then sort all the items so that they appear sequentially under the order no, removing data that does not have the order no next to the cat no so should look something like this:
order no ICN Product QTY
EGU-ORD/21-11-1547 6016020 Pethidine 50mg/mL Ampoule [INJ] 400
EGU-ORD/21-11-1551 6200805 Acetazolamide 250mg Tablet [PO] 72
EGU-ORD/21-11-1551 6203655 Allopurinol 300mg Tablet [PO] 200 removing lines containing Total line items, Total lines received, OrderNo once aligned left etc

ICN	Product
EGU-ORD/21-11-1547
6016020	Pethidine 50mg/mL Ampoule [INJ]
Total Line Items:
Total Lines Fully Received:
EGU-ORD/21-11-1551
6200805	Acetazolamide 250mg Tablet [PO]
6203655	Allopurinol 300mg Tablet [PO]
6803615	Aluminium Hydroxide 300mg/5mL Gel [PO]
6051013	Amitriptyline 25mg Tablet [PO]
6147016	Ibuprofen 400mg Tablet [PO]
6147084	Ibuprofen 400mg Tablet [PO]
6441816	Insulin, Soluble And Isophane 30/70%, Brown 100IU/mL Syringe, Prefilled [SC]
6441788	Insulin, Soluble, Fast Acting, Yellow 100IU/mL Syringe, Prefilled [SC]
ICN	Product
EGU-ORD/21-11-1551
6441532	Insulin, Soluble, Fast Acting, Yellow 100IU/mL Vial [SC]
6844230	Lactulose 3.35g/5mL Syrup [PO]
6862830	Liquid Paraffin BP Liquid [PO]
6248310	Loperamide 2mg Tablet [PO]
6156056	Metformin 500mg Tablet [PO]
Total Line Items:
Total Lines Fully Received:
EGU-ORD/22-01-1595
6404430	Amoxycillin, Clavulanic Acid 1000&200mg Vial [INJ]
Total Line Items:
Total Lines Fully Received:

 

[DanteAmor]

Assuming the data starts in cell A1, the QTY is in column C, like this:

Dante Amor
	A	B	C
1
2	CN	Product
3	EGU-ORD/21-11-1547
4	6016020	Pethidine 50mg/mL Ampoule [INJ]	400
5	Total Line Items:
6	Total Lines Fully Received:
7	EGU-ORD/21-11-1551
8	6200805	Acetazolamide 250mg Tablet [PO]	72
9	6203655	Allopurinol 300mg Tablet [PO]	200
Hoja4

The result in cell E2 onwards, like this:

Dante Amor
	E	F	G	H
1
2	EGU-ORD/21-11-1547	6016020	Pethidine 50mg/mL Ampoule [INJ]	400
3	EGU-ORD/21-11-1551	6200805	Acetazolamide 250mg Tablet [PO]	72
4	EGU-ORD/21-11-1551	6203655	Allopurinol 300mg Tablet [PO]	20
Hoja4

Try this:

Sub Rearrange()
  Dim c As Range, num As String
  For Each c In Range("A1", Range("A" & Rows.Count).End(3))
    If Left(c.Value, 3) = "EGU" Then num = c.Value
    If Left(c.Value, 1) Like "*[0-9]*" Then Range("E" & Rows.Count).End(3)(2).Resize(1, 4).Value = Array(num, c, c.Offset(, 1), c.Offset(, 2))
  Next
End Sub

 

[NKUNZI]

Much appreciated,
Not exactly what I needed but is in the right ballpark, so gives me something to work with. Apologies I failed to say and clarify that this data should be placed in a new workbook. Where what I need in essence are rows containing the order no, icn no, product, qty and price.
The thinking was that if I can see how the icn & products are aligned then I can expand along similar lines for qty and price.
So based on the data that I gave you, you cracked it 100% and for this I am truly grateful, it allows me to work with it and then assign to a new workbook and worksheet.
Would appreciate if I can call on your help if I get stuck in that area.