I want to have a new spreadsheet that pulls the position
number, percentage allocated, and the accounts. I know how to easily pull
this information in when there is only one line. The problem is that for one
position number there may be multiple percentages along with different
accounts. (Note: The end goal is to run a pivot table on the information so
I would prefer to have multiple rows vs multiple columns.)

Employee Key Spreadshee
This has one line per employee. One employee holds only one position
number, but the position number could be allocated to more than one
department).

Percentage Key Spreadsheet (
Pos # % Alloc Dept Exp
1A 100 200 50000
2A 95 205 50001
2A 5 204 50001


New Spreadsheet (pulling and being populated with the information from
Percentage Key spreadsheet and employee key spreadsheet)

Pos# Emp % Alloc Dept Exp Salary
1A Jane D 100 200 50000 100.00
2A John D 95 205 50001 95.00
2A John D 5 204 50001 5.00


Thanks in Advance

It looks like you need a custtom macro. The problem is you don't know how
many entries each employee has.

If the 2nd table (thhe one with the person name) already exist, then the
problem is is simplier. Then you are looking for either the 1st, 2nd or 3rd
time a POS in the Key spreadsheet.

If you have a table like

A
B
C
C
D
E

and you want tto get the 2nd occrance of C. You can do something like this

=Offset(A1,match("C",A1:A100,0),0,1,1) + 1),1,1)
This will find the 1st occurance of C and then get the value att the next row.

"michelle" wrote:

I want to have a new spreadsheet that pulls the position
number, percentage allocated, and the accounts. I know how to easily pull
this information in when there is only one line. The problem is that for one
position number there may be multiple percentages along with different
accounts. (Note: The end goal is to run a pivot table on the information so
I would prefer to have multiple rows vs multiple columns.)

Employee Key Spreadshee
This has one line per employee. One employee holds only one position
number, but the position number could be allocated to more than one
department).

Percentage Key Spreadsheet (
Pos # % Alloc Dept Exp
1A 100 200 50000
2A 95 205 50001
2A 5 204 50001


New Spreadsheet (pulling and being populated with the information from
Percentage Key spreadsheet and employee key spreadsheet)

Pos# Emp % Alloc Dept Exp Salary
1A Jane D 100 200 50000 100.00
2A John D 95 205 50001 95.00
2A John D 5 204 50001 5.00


Thanks in Advance

I am lost, sorry. I am unfamiliar with the Offset function. The new
spreadsheet shown at the bottom is what I want the spreadsheet to look like.
There is no way to populate or essentially "explode" that information from
the employee key and percentage key to the "new worksheet"?

"Joel" wrote:

It looks like you need a custtom macro. The problem is you don't know how
many entries each employee has.

If the 2nd table (thhe one with the person name) already exist, then the
problem is is simplier. Then you are looking for either the 1st, 2nd or 3rd
time a POS in the Key spreadsheet.

If you have a table like

A
B
C
C
D
E

and you want tto get the 2nd occrance of C. You can do something like this

=Offset(A1,match("C",A1:A100,0),0,1,1) + 1),1,1)
This will find the 1st occurance of C and then get the value att the next row.

"michelle" wrote:

I want to have a new spreadsheet that pulls the position
number, percentage allocated, and the accounts. I know how to easily pull
this information in when there is only one line. The problem is that for one
position number there may be multiple percentages along with different
accounts. (Note: The end goal is to run a pivot table on the information so
I would prefer to have multiple rows vs multiple columns.)

Employee Key Spreadshee
This has one line per employee. One employee holds only one position
number, but the position number could be allocated to more than one
department).

Percentage Key Spreadsheet (
Pos # % Alloc Dept Exp
1A 100 200 50000
2A 95 205 50001
2A 5 204 50001


New Spreadsheet (pulling and being populated with the information from
Percentage Key spreadsheet and employee key spreadsheet)

Pos# Emp % Alloc Dept Exp Salary
1A Jane D 100 200 50000 100.00
2A John D 95 205 50001 95.00
2A John D 5 204 50001 5.00


Thanks in Advance

There is no simple way of doing what you want to do. An undrstanding of
some of the more complicated functions or learning VBA is the only waty of
going.

If somebbody is going to help you , then more specific information is needed
on the way you workbook is organized such as sheet names, specific columns
and rows. Take a look at some of the responses on the Excell Programming
discussions which might give you some help. Look at the way people asked the
questions. Some questions are ignored because they do not give enough
information to provide a solution.

The major problem people have with excel is to solve problems where data is
on multiple rows. You problem is not unique.

"michelle" wrote:

I am lost, sorry. I am unfamiliar with the Offset function. The new
spreadsheet shown at the bottom is what I want the spreadsheet to look like.
There is no way to populate or essentially "explode" that information from
the employee key and percentage key to the "new worksheet"?

"Joel" wrote:

It looks like you need a custtom macro. The problem is you don't know how
many entries each employee has.

If the 2nd table (thhe one with the person name) already exist, then the
problem is is simplier. Then you are looking for either the 1st, 2nd or 3rd
time a POS in the Key spreadsheet.

If you have a table like

A
B
C
C
D
E

and you want tto get the 2nd occrance of C. You can do something like this

=Offset(A1,match("C",A1:A100,0),0,1,1) + 1),1,1)
This will find the 1st occurance of C and then get the value att the next row.

"michelle" wrote:

I want to have a new spreadsheet that pulls the position
number, percentage allocated, and the accounts. I know how to easily pull
this information in when there is only one line. The problem is that for one
position number there may be multiple percentages along with different
accounts. (Note: The end goal is to run a pivot table on the information so
I would prefer to have multiple rows vs multiple columns.)

Employee Key Spreadshee
This has one line per employee. One employee holds only one position
number, but the position number could be allocated to more than one
department).

Percentage Key Spreadsheet (
Pos # % Alloc Dept Exp
1A 100 200 50000
2A 95 205 50001
2A 5 204 50001


New Spreadsheet (pulling and being populated with the information from
Percentage Key spreadsheet and employee key spreadsheet)

Pos# Emp % Alloc Dept Exp Salary
1A Jane D 100 200 50000 100.00
2A John D 95 205 50001 95.00
2A John D 5 204 50001 5.00


Thanks in Advance