Complicated Lookup/Index

[rjhh2013]

Hi,

I have been tinkering with a spread sheet in order to make it easier to navigate. The data is exported straight from our system and then dumped in (A1) the workbook.

The data layout is the same each month regarding the columns but the number of Rows differs each month.

The reason this is a problem is because I need to create a new column at the end of the data (N). N to be completed if there is a Value in Column A and I want the value to be the name from F16. So the name would be in N2:N5,N9 & N10.

This data is repeated for 2000 or so rows with different people, and the titles in A change, increase or decrease depending on whether the employee has a pension, is part of the union etc.

Help!

[TABLE="width: 1008"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]Code
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Period
[/TD]
[TD]Rate
[/TD]
[TD][/TD]
[TD]Quantity
[/TD]
[TD][/TD]
[TD]Gross
[/TD]
[TD]Employer NIC
[/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD="colspan: 3"]ACTS - UNITE UNION
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD="colspan: 2"]EE AVC xx%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD="colspan: 2"]ERS AVC xx%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD]PENSION
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD][/TD]
[TD="colspan: 2"]Sub Total 2 by
[/TD]
[TD][/TD]
[TD="colspan: 2"]Deductions
[/TD]
[TD][/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9
[/TD]
[TD="colspan: 2"]BASIC PAY
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[/TR]
[TR]
[TD="align: right"]10
[/TD]
[TD="colspan: 2"]SAL SAC PENSION
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2
[/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[/TR]
[TR]
[TD="align: right"]11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12
[/TD]
[TD][/TD]
[TD="colspan: 2"]Sub Total 2 by
[/TD]
[TD][/TD]
[TD]Earnings
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[/TR]
[TR]
[TD="align: right"]13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15
[/TD]
[TD="colspan: 2"]Sub Total 1 by
[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]625540 D. Smith
[/TD]
[TD][/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD][/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[TD]xx
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

I see that "name" is in Column F.

Is anything else in column F, other than name ?

 

[rjhh2013]

I see that "name" is in Column F.

Is anything else in column F, other than name ?

The Name is in Column E - it is preceded by a numerical value.

Also in the columnb are "Deductions" and "Earnings"


Thanks

 

[rjhh2013]

I have tried as a quick thought to do this - in Column U:

=IF(LEN(F20)>10,F20,"")

Then in Column F:

=IF(B7="","",INDEX(U:U,MATCH("*",U:U,0)))


There are two rpoblems with this - one - it returns a blank cell as there is a formula there and two even if I removed the formulas from the blank cells it would still only return the first name on the list all the way down.

 

[Gerald Higgins]

From post #1

...I want the value to be the name from F16

From post #2

I see that "name" is in Column F.

From post #3

The Name is in Column E

OK. . . . .


It seems that the first part of the challenge is to determine which entries in Column E are names, that you want to transpose to Col N, and which entries are NOT names, that you want to ignore.

Maybe something like this for Col U

=if(isnumber(left(e20,1)+0),e20,u21)

This assumes all names are preceded by a numeric value.

An alternative would be

=if(or(E20="Deductions",E20="Earnings",E20=""),U21,E20)

which would basically ignore any entry in E20 that is either blank, or one of the specified text strings, meaning anything else was assumed to be a name.

Either of these SHOULD populate col U with the correct name, all the way down.

Does that work for you ?

If yes, it should be fairly simple to just pull the relevant name across for the specific rows that require a name.