multiple field values entered in blank rows...

[garzajoe]

I have searched for a solution, but haven't found one, so I'm posting my problem here. I've inherited spreadsheets that were built incorrectly, so that a particular field with multiple values has the values entered as though they were separate records, i.e., in subsequent rows - all other data on the following rows would/should be the same, such as a person's name, a date, and so on, but this one field. if there are 5 values for that field, the row would repeat 5 times.
problem #1. except for the field in question, the rest of the row is completely blank, so, basically, a "record" consists of multiple rows.
problem #2. not every record has more than one value in that field, and they do not have the same number of entries - some records might have 5 entries, some might have 3, etc. there is no consistency.
If i were doing this in access, for example, i might try using sub-tables. if i could keep data in a merge, i might just merge the cells with the multiple data.


when I try to make a pivot table out of this data, excel sees the blanks in the following rows as unique records (of course) and makes the pivot table worthless.

any suggestions?
thanks for your time.
jg

 

[63falcondude]

Depending on how many columns you are working with, you may be able to use a formula to rebuild your data for each column with the missing data. For example:

Excel 2010
	A	B	C
1	Color	Color	Item
2	Blue	Blue	Sky
3	Blue		Water
4	Red	Red	Cherry
5	Green	Green	Grass
6	Green		Tree
7	Green		Button
Sheet1

Cell Formulas
Range	Formula
A2	=IF(B2="",A1,B2)

 

[garzajoe]

I wish it were that easy. there are hundreds of rows, and there will be a large number of spreadsheets like this. Certainly that solution will resolve it one record at a time, but then we just wind up with duplicate records that differ only in that one field. Ideally, the solution will be a "wholesale" fix that puts all the data from that repeated filed either into one cell, or into some kind of linked table or some such, with one command - doing it one record at a time is non-starter.
thanks for your time!

 

[63falcondude]

I wish it were that easy. there are hundreds of rows, and there will be a large number of spreadsheets like this. Certainly that solution will resolve it one record at a time, but then we just wind up with duplicate records that differ only in that one field. Ideally, the solution will be a "wholesale" fix that puts all the data from that repeated filed either into one cell, or into some kind of linked table or some such, with one command - doing it one record at a time is non-starter.
thanks for your time!

This is for one column at a time, not one record at a time. You would drag the formula down the entire column. Even doing one column at a time can be cumbersome if you have a lot of columns to do. It sounds to me like you are looking for a VBA approach. Unfortunately I can not help with that.