transfer data from multiple rows to one single row

hi everybody..

i need to do the following to a large number of data:

current aspect of table:

name1____data11____(empty)____(empty)____(..etc)
name1____(empty)___data12_____(empty)____(..etc)
name1____(empty)__(empty)_____data13_____(..etc)
..(etc)
name2____data21____(empty)____(empty)____(..etc)
name2____(empty)___data22_____(empty)____(..etc)
name2____(empty)__(empty)_____data23_____(..etc)
..(etc)

desired result:

name1____data11____data12____data13____(..etc)
name2____data21____data22____data23____(..etc)
..(etc)

I think you get the picture..In general I have some rows of data for the same name with data in a single column in each row and I need to replace them with a single row and data in the corresponding columns...The same for the next name..
I hope somebody has a solution because copy/paste is really time consuming..

thanks..

For the range of A1:whatever


try this

Please Login or Register  to view this content.

Copy G1 right to where you need
then copy the first row start from F1
paste down to the last name row

Then you can get a range of data with the correct result and some rubbish as well
Firt copy and paste specil value
Then, Sort the data and delete the rubbish, you will get what you need,

sglife u r gr8..thanks a lot..

i have only one problem...in the data cells there are numbers (0-30) and 0 means something.With your formula I get
0 in the empty cells which can be a problem. i'll give you a more detailed view of my table, if you can come up
with something.

(# corresponding to a certain month entry) (name) (hire date) (leave date) [months (jan to dec) with 4 subfields
each for the different type of absences] :total 48 columns

example:
___________________________january___february__march________april .....
1_John_date_date___________0 1 1 0_____________________________________________(for january)
2_John_date_date_____________________2 1 0 0___________________________________(for february)
3_Mike_date_date______________________________0 1 1 0__________________________(for march)
4_Mike_date_date___________________________________________2 1 0 0_____________(for april)
1_Mary_date_date___________2 1 1 0_____________________________________________(for january)
2_Mary_date_date_____________________3 0 0 1___________________________________(for february)


As you can see the first column always refers to a specific set of columns (corresponding to the appropriate
month) that only them contain usable data in each row. maybe I can use that...?

@ cheeky charlie, the number of rows in each name is not the same..that 's because each person is hired for different time periods..(i.e. feb-sep, jun-mar, mar-aug etc.). So i think i can't use such a standardized formula.

@ sglife..ok i tried it on the original document and it doesn't work for 2 reasons..the first is the problem with the zeros from the offset function, the second one is that i have 4 cells in the same row and then 4 cells in the next row etc.. your function goes like a ladder one cell at the time...i need 4 cells ladder... i tried to change the function accordingly but no successful result.. i think the trick might be on the first cell that tells the month each row refers to..but i can't grasp a solution..