I have a problem that I do not know how to solve.

Facts:
I have data in a text file that I can easily import into excel. The problem
is manipulating the data after it is in excel. The data comes in in the
following format:
Column A Column B
Employee ID: 123456 Key Data
Employee Name Smith Key Data
Other info Other info
Other info Other info
Pay Info1 123,456 Key Data
Pay Info2 789,123 Key Data
Total Pay 1,234,567 Key Data
Other info Other info
Department Accounting Key Data
Other info Other info

There is a blank row and then the next record starts in the same format.
Some records contain the other info fields others do not. However, all
records contain the Key Data fields.

I want to take this info and get it into the following format:
Column A Column B Column C Column D Column E Column F
Employee ID Employee Name PayInfo1 Pay Info2 Total Pay Department

The I can sort by Department, sort from highest Total Pay to lowest etc.
Each month I will need to do this so some records may change others may not.
New records may be added or employees may change department.

Can I do what I want to do or do I need to programming help?

Hi, EmmaJade-
I mocked up your data and got a solution by copying the 10 row, 2
column sample data starting at A4. The next records start in A15, A26,
A37, and A48.

Range E2:J2 contain the integers 1 2 5 6 7 9
Range E3:J3 contain your headers: Employee ID, Employee Name, Pay
Info1, Pay Info2, Total Pay, Department
Range D4:D8 contain the integers 0 1 2 3 4

The formula in cell E4 is
=OFFSET($B$3,$D4*10+E$2,0)
which you can copy and paste into the range E4:J8. This will convert
the columnar format to rows.

If you'd like, I'll email this workbook to you- send an email address
(preferably a "blind" email address such as hotmail or yahoo) to me at
cyclezen AT yahoo DOT com.

Oops- the formula is this:

=OFFSET($B$3,$D4*11+E$2,0)

Sorry 'bout that, hope you're not off trying to make that wrong formula
work. This formula assumes that a single blank row appears between the
records.

"Dave O" wrote:

Oops- the formula is this:

=OFFSET($B$3,$D4*11+E$2,0)

Sorry 'bout that, hope you're not off trying to make that wrong formula
work. This formula assumes that a single blank row appears between the
records.


That is great. Now how does this work if I have 10 records in a row. I
want the output changed but I want the output as follows:

Employee ID, Employee Name, Pay Info1, Pay Info2, Total Pay, Department

123456 Smith 123,456 456,789 1,234,567
Accounting
456789 Jones 807,456 456,123 1,456,789
Engineering
etc.

The way I understand your formula it works if I set it up beside each record
but then I have rows between each converted record. Do you understand what I
am saying.

I was also thinking that the raw data would be in a worksheet called Data1
and the output would be in a worksheet called Output1. Then I could
manipulate the data in output1 to sort in different ways.

"EmmaJade" wrote:



"Dave O" wrote:

Oops- the formula is this:

=OFFSET($B$3,$D4*11+E$2,0)

Sorry 'bout that, hope you're not off trying to make that wrong formula
work. This formula assumes that a single blank row appears between the
records.


That is great. Now how does this work if I have 10 records in a row. I
want the output changed but I want the output as follows:

Employee ID, Employee Name, Pay Info1, Pay Info2, Total Pay, Department

123456 Smith 123,456 456,789 1,234,567
Accounting
456789 Jones 807,456 456,123 1,456,789
Engineering
etc.

The way I understand your formula it works if I set it up beside each record
but then I have rows between each converted record. Do you understand what I
am saying.

I was also thinking that the raw data would be in a worksheet called Data1
and the output would be in a worksheet called Output1. Then I could
manipulate the data in output1 to sort in different ways.


OK I have realized the problem I was having. What you did was perfect -
thank you. The problem I was having was that each record was not the same
length.
Some records had the extra info others did not. I have now made the records
the same size so the spacing between them is the same. Thanks again!

Glad to help! Sometimes it's difficult to describe a spreadsheet in
detail- glad it worked out for you. Enjoy the holidays!