Add Data to a new column from a CEL to create a dataset.

[Murds]

Hello all,

I'm completely stumped with something on excel. I'd like to think of myself as a decently proficient user but I just cant get to the bottom of this. I've started a new job in a call centre and we have data that is annoyingly formatted. We have multiple customers and it is formatted a bit like this:

[TABLE="width: 100"]
<tbody>[TR]
[TD]Customer 1[/TD]
[TD]Calls inbound[/TD]
[/TR]
[TR]
[TD]range 1
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]range 2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]range 3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]range 1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]range 2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]range 2[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

What I really want is to add the customer name to a new column so I can pivot the data. So is there a formula that will allow me to fill in a new column based on the customer name (there's over 80 customers).

Essentially so the data will look like this:

[TABLE="width: 200"]
<tbody>[TR]
[TD]Customer 1[/TD]
[TD]Customer 1[/TD]
[TD]inbound calls[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]Range 1[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]Range 2[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]Range 3[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]Customer 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]Range 1[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]Range 2[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]Range 3[/TD]
[TD]26[/TD]
[/TR]
</tbody>[/TABLE]

Any help is greatly appreciated.

 

[Fluff]

How about

Excel 2013/2016
	A	B	C
1	Customer 1	Customer 1	Calls inbound
2	Customer 1	range 1	1
3	Customer 1	range 2	2
4	Customer 1	range 3	5
5	Customer 2	Customer 2
6	Customer 2	range 1	1
7	Customer 2	range 2	5
8	Customer 2	range 2	6
Run Macros

Cell Formulas
Range	Formula
A1	=B1
A2	=IF(LEFT(B2,8)="Customer",B2,A1)

 

[Murds]

How about

Excel 2013/2016

	A	B	C
Customer 1	Customer 1	Calls inbound
Customer 1	range 1
Customer 1	range 2
Customer 1	range 3
Customer 2	Customer 2
Customer 2	range 1
Customer 2	range 2
Customer 2	range 2

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]6[/TD]

</tbody>

Run Macros​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A1[/TH]
[TD="align: left"]=B1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=IF(LEFT(B2,8)="Customer",B2,A1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This worked an absolute treat!

 

[Fluff]

You're welcome & thanks for the feedback