change EXCEL emailed spreadsheet

[Kim296]

I have received an excel spreadsheet that someone converted over from another program. It contains information for one person in multiple columns and rows. I would like to convert this to show only one persons information in one row, so that I can sort and filter the information. Does anyone know of a way to formulate a new sheet to pull from multiple rows and columns to make the information flow with one person on each row?

Example:
One person's information is as follows:
A1 information
A2 more info
A3 more info
A4 more infor
B1 same person more info
B2 same person more info
B3 same person more info
B4 same person more info
C1 same person more info
C2 same person more info
C3 same person more info
C4 samer person more info

A5 new person info
A6 same new person info
A7 same new person info
A8 same new person info
B5 same new person info
and so on, so on, so on.

Each person takes up 4 rows and multiple columns. The data base is 12017 rows long and 7 colums wide. I would like to convert the spreadsheet to show each of the 4 rows into 1 row for each person. Can anyone help with a solution?

Thanks,
Kim

 

[JRB-Bldr]

My first question for these types of issues is:

Is this a one-time need?​

Or will this need to be done often?​

If one-time, then you can do the majority of the work in Excel itself and get the data as you need it and THEN bring it into a VFP data table.

But if you need to do this often, then you might want to think about doing the work in VFP by using Excel Automation.

Let us know so that we can better assist you.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

I agree with JRB. This is a problem that is best solved within Excel itself - unless of course it is part of a bigger process that you are doing via VFP Automation.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Kim296]

Thank you both for your reply.

This is a one time thing (I hope). I was able to find a solution to my problem.

On a new spreadsheet, I inserted the following formula for each column needed:

=OFFSET(Sheet1!$A$1,(ROW()-1)*4,0)

When pulling the formula down, it grabbed each cell that was needed per column header.

Thanks for all of your feedback.
Kim

 

[GriffMG]

Does that mean you have sorted yourself out?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[Kim296]

Yes GriffMG, Thank you.