Combining data from multiple rows

[Sarah (OGI)]

I have a spreadsheet containing just over 4000 rows (including a header row).
The number of rows will increase each month as more data is added.
The data is extracted from another system and is not particularly
user-friendly, as explained below. Basically, I want to combine data from
multiple rows.

At present, there are varying number of rows associated with each customer -
one customer may have 10 rows, one may have 12, one may have 3, etc..
Columns B, C and D will always contain data (Customer ID, Customer Name and
Status).
Only one of the rows for each customer (never the same between customers,
i.e. never always the 3rd row, or never always the 5th row, etc) will show a
value in column A. Policy values, relating to specific months, will be shown
in columns E to Z on an undetermined number of rows (undetermined, as one
customer might show policy values for 3 months, i.e. 3 separate rows, or
values for just 1 month, i.e. 1 row). Another row might show data
(numerical, dates and text) in varying columns AA thru to CB. Any rows
remaining for each customer will show values in columns B, C and D only.
For each row containing data in columns E to Z, I need to copy through the
value in column A and any values in columns AA to CB.

Agency ID / Co. ID / Name / Status / E - Z / AA - CB
(blank) / 1234 / Company A / Main / (blank) / (blank)
9999 / 1234 / Company A / Main / (blank) / (blank)
(blank) / 1234 / Company A / Main / Currency Data / (blank)
(blank) / 1234 / Company A / Main / (blank) / Numerical and text data
(blank) / 1234 / Company A / Main / (blank) / (blank)
(blank) / 2468 / Company A / Main / (blank) / (blank)
(blank) / 2468 / Company B / Main / Currency Data for Month 1 / (blank)
(blank) / 2468 / Company B / Main / Currency Data for Month 2 / (blank)
8888 / 2468 / Company B / Main / (blank) / (blank)
(blank) / 2468 / Company A / Main / (blank) / (blank)
(blank) / 2468 / Company B / Main / (blank) / Numerical and text data


The result should be:

Agency ID / Co. ID / Name / Status / E - Z / AA - CB
9999 / 1234 / Company A / Main / Currency Data / Numerical and text data
8888 / 2468 / Company B / Main / Currency Data for Month 1 / Numerical and
text data
8888 / 2468 / Company B / Main / Currency Data for Month 2 / Numerical and
text data

(the numerical and text data for multiple rows for one customer will be the
same)

I hope someone can help - I just want advice re how to do this in the most
easiest way (if there is one)? Any ideas?

 

[Jim Rech]

Well, I'm lost but it does seem that your results include only rows with
entries in column A. If that is a start, at least, you might sort your data
by column A, descending. After that all the rows with no entries in column
A should be at the bottom. A step in the right direction I hope.

--
Jim
|I have a spreadsheet containing just over 4000 rows (including a header
row).
| The number of rows will increase each month as more data is added.
| The data is extracted from another system and is not particularly
| user-friendly, as explained below. Basically, I want to combine data from
| multiple rows.
|
| At present, there are varying number of rows associated with each
customer -
| one customer may have 10 rows, one may have 12, one may have 3, etc..
| Columns B, C and D will always contain data (Customer ID, Customer Name
and
| Status).
| Only one of the rows for each customer (never the same between customers,
| i.e. never always the 3rd row, or never always the 5th row, etc) will show
a
| value in column A. Policy values, relating to specific months, will be
shown
| in columns E to Z on an undetermined number of rows (undetermined, as one
| customer might show policy values for 3 months, i.e. 3 separate rows, or
| values for just 1 month, i.e. 1 row). Another row might show data
| (numerical, dates and text) in varying columns AA thru to CB. Any rows
| remaining for each customer will show values in columns B, C and D only.
| For each row containing data in columns E to Z, I need to copy through the
| value in column A and any values in columns AA to CB.
|
| Agency ID / Co. ID / Name / Status / E - Z / AA - CB
| (blank) / 1234 / Company A / Main / (blank) / (blank)
| 9999 / 1234 / Company A / Main / (blank) / (blank)
| (blank) / 1234 / Company A / Main / Currency Data / (blank)
| (blank) / 1234 / Company A / Main / (blank) / Numerical and text data
| (blank) / 1234 / Company A / Main / (blank) / (blank)
| (blank) / 2468 / Company A / Main / (blank) / (blank)
| (blank) / 2468 / Company B / Main / Currency Data for Month 1 / (blank)
| (blank) / 2468 / Company B / Main / Currency Data for Month 2 / (blank)
| 8888 / 2468 / Company B / Main / (blank) / (blank)
| (blank) / 2468 / Company A / Main / (blank) / (blank)
| (blank) / 2468 / Company B / Main / (blank) / Numerical and text data
|
|
| The result should be:
|
| Agency ID / Co. ID / Name / Status / E - Z / AA - CB
| 9999 / 1234 / Company A / Main / Currency Data / Numerical and text data
| 8888 / 2468 / Company B / Main / Currency Data for Month 1 / Numerical and
| text data
| 8888 / 2468 / Company B / Main / Currency Data for Month 2 / Numerical and
| text data
|
| (the numerical and text data for multiple rows for one customer will be
the
| same)
|
| I hope someone can help - I just want advice re how to do this in the most
| easiest way (if there is one)? Any ideas?

 

[Duke Carey]

According to your summary, many rows have blanks (NULLs) in E-Z and also in
in AA-CB, and that those rows are to be eliminated, right?

First, make a copy of your data and work on that copy

Next, insert a new column A and number each row in ascending order. Be sure
to convert these numbers to values.

Next use the Counta(F2:CC2) formula in cell CD2 to find the number of
entries in those columns.

Now copy that formula down for all rows and sort on column CD2.

All the rows you want to ignore are at the top, so delete all the rows with
a zero in column CD, then re-sort by column A.

You won't be finished yet, but a lot of the irrelevant data will be gone and
then maybe you can see a way to deal with it