Jon von der Heyden
MrExcel MVP, Moderator
- Joined
- Apr 6, 2004
- Messages
- 10,907
- Office Version
- 365
- Platform
- Windows
Hi All
I have a column of mixed information. Every now and then the value refers to a cost centre which is identified as beginning with "cc-". The other values could be other master data fields or even transactional values.
I wish to create a new column called "cost centre" which will always reflect the previously found cost centre. The table below shows the desired output.
Excel 2013
<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
So far my attempts have lead me to this, although I seem unable to reference the identified cost centre from the same column, but from the record above. I get an error produced by the Else statement in CC1; "The field 'Cost Centre1' of the record wasn't found.".
...where Label is the column of mixed values
Hope this makes sense.
Regards
Jon
I have a column of mixed information. Every now and then the value refers to a cost centre which is identified as beginning with "cc-". The other values could be other master data fields or even transactional values.
I wish to create a new column called "cost centre" which will always reflect the previously found cost centre. The table below shows the desired output.
Excel 2013
A | B | |
1 | Original | New Col Results |
2 | Values | Cost Centre |
3 | cc-100 | cc-100 |
4 | gasdj | cc-100 |
5 | jbh s | cc-100 |
6 | 4598 | cc-100 |
7 | cc-101 | cc-101 |
8 | snkhj | cc-101 |
9 | 109 | cc-101 |
10 | bjhgsa | cc-101 |
11 | cc-102 | cc-102 |
12 | kjhnd | cc-102 |
13 | jkhsd | cc-102 |
<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet5
So far my attempts have lead me to this, although I seem unable to reference the identified cost centre from the same column, but from the record above. I get an error produced by the Else statement in CC1; "The field 'Cost Centre1' of the record wasn't found.".
Code:
let
Source = Excel.CurrentWorkbook(){[Name="tblCC"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Label", type any}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
CC1 = Table.AddColumn(#"Added Index", "Cost Centre1", each if Text.Start(Text.From([Label]),3)="cc-" then Text.From([Label]) else #"Added Index"{[Index]-1}[Cost Centre1])
in
CC1
...where Label is the column of mixed values
Hope this makes sense.
Regards
Jon
Last edited: