Return last 3 Columns

[Sara]

Hi There

I have a particularly nasty PDF file to deal with every month which spans many many pages. I've created a copy and paste area in a spreadsheet which then runs a formula based Text to Columns.

From this I need to pull selected data
- the initial 6 digit numeric code
- the type
- and then the set of 3 numbers at the end

For example
864092 CEA Highbrook 3PL Team 2 72.00 1,400.00 5.14
Becomes

RC Dept; Type; Hours; Std; %
864092; CEA; 72.00; 1,400.00; 5.14



The formula I've written is big and ugly and wrong.
Can anyone help me re-write it?

 

[NARAYANK991]

Hi Sara ,

I have downloaded your file , and I am copying & pasting the data which is in column A here :

814461 CEA CP CCC 613.97 9,532.50 6.44
All 645.97 10,444.50 6.18
[ECLCRA] - Admin - Central Region
895338 CEA Central Region Admin 28.25 652.09 4.33
895338 MgrSpec Central Region Admin 12.00 480.00 2.50
All 40.25 1,132.09 3.56
[ECLCRA] - Admin - Central Region 40.25 1,132.09 3.56
892762 CEA Processing PM 433.25 11,443.77 3.79
892762 MgrSpec Processing PM 48.00 480.00 10.00
All 481.25 11,923.77 4.04
895125 CEA Processing Day 70.50 4,495.87 1.57
895125 MgrSpec Processing Day 0.00 240.00 0.00
All 70.50 4,735.87 1.49
[ECLOPM] - Print Media
869043 MgrSpec Netlink 16.00 240.00 6.67
All 16.00 240.00 6.67
[ECLCLCP] - Copsey Place
864092 CEA Highbrook 3PL Team 2 72.00 1,400.00 5.14
All 72.00 1,400.00 5.14
[ECLCLCP] - Copsey Place 72.00 1,400.00 5.14
[ECLCLHB] - Highbrook Operations
857304 CEA WH2 Inwards 123.00 2,589.58 4.75
857304 MgrSpec WH2 Inwards 0.00 240.00 0.00
All 123.00 2,829.58 4.35
882210 CEA WH2 Outwards 24.00 1,200.00 2.00
882210 MgrSpec WH2 Outwards 0.00 240.00 0.00
All 24.00 1,440.00 1.67

I assume your question is with reference to the above data ; is this correct ?

If so , do I understand your requirement as only those lines which start with a 6-digit number need to be converted ?

If so , what will be the type in the following line ?

882210 MgrSpec WH2 Outwards 0.00 240.00 0.00

Narayan

 

[Sara]

Hi Narayan

Sorry for not being clear.
Column A is the area to split.
From this I need to pull lines that start with a 6-digit number or "All"

The line you specified and the one immediately below it:
882210 MgrSpec WH2 Outwards 0.00 240.00 0.00
All 24.00 1,440.00 1.67

need to become
RC Dept; Type; Hours; Std; %
882210; MgrSpec; 0.00; 240.00; 0.00
882210; All; 24.00; 1,440.00; 1.67

There are only 3 different "Type" which are "All", "CEA" and "MgrSpec"

Sorry again for not being clear.

Regards
Sara

 

[NARAYANK991]

Hi Sara ,

No issues ; let me summarise the logic as follows :

1. If the first character is a "[" , then do nothing ; is this correct ?

2. If the first character is "A" , then repeat the RC Dept from the line above this line.

3. If neither of the above is true , then RC Dept is put to the left most 6 digits.

4. If the first character is not "[" , then Type is put to what ever text is to be found in the input i.e. if All is found , then Type = All , if CEA is found , then Type = CEA , if MgrSpec is found then Type = MgrSpec.

5. If the first character is not "[" , then the number after the last space goes into the % column , the number before that goes into the STD column , and the number before that goes into the Hours column. We can also take the number of space characters in the input text , and take the numbers which occur at the appropriate positions e.g. if the number of spaces is 3 ( as it is in the string All 72.00 1,400.00 5.14 ) , we take the numbers which occur after the 1st , 2nd and 3rd spaces ; if the number of spaces is 8 ( as it is in the string 864092 CEA Highbrook 3PL Team 2 72.00 1,400.00 5.14 ) , we take the numbers which occur after the 6th , 7th and 8th spaces.

If all of the above is correct , the formula itself is bound to be ugly !

Narayan

 

[Sara]

Hi Narayan

All the logic stated is spot on.
My attempt handles this with lots of helper columns, I just got stuck on the number part at the end (Col Y:AA)
Col H deals with the irrelevant lines
Col I and J pull the 6 digit code and type
Col K - X run the text to column function in formulas

Pulling everything back into columns B:G for copying and pasting to do analysis and charting.

The major problem is the difference and variety of Team names between Type and Hours.
I thought there might be a way of handling it with the Right text function mixing it with Find to locate the last 3 spaces, but I couldn't get the logic right.
The formula I wrote in Col Y:AA is incorrect as it is pulling the first 3 number sets, which are not necessarily the same as the last 3.

I'm ok with Big and Ugly formulas that work...

Sara

 

[NARAYANK991]

Hi Sara ,

Can you check your file here ?

I have also used big and ugly formulae ! Hopefully they do the job.

Narayan

 

[Sara]

Narayan - you are a genius, and what's more, I can understand all your formulas!