Rearranging a report

[Rick]

Excel 2003.
A report has in col A, 4 Headers (or segment titles) for the report which is
in 4 parts, in Sheet1. Also data is in Col A below the 4 Headers. Also in
Col E is data that corresponds with the labels in Col A (on the same rows).
However the report does not come thru with the 4 Headers/segment titles in
the same row in Col A all the time, it varies. Also the data shifts rows,
and is not in the same place all of the time.

I wanted to rearrange the data so that it is in the same rows/cells all of
the time, by linking them to a different tab in the same workbook, Sheet2.
That way all of the Headers and data are in the same place each time on
Sheet2.

I have tried Vlookup, Match, Mid, Hlookup, Offset, and everything else but
it will not bring the 4 Headers and data from Sheet1 to Sheet2.

The 4 Headers have the same title text in them each time. So I tried to key
off of that via Offset and everything else, but was not successful.

I want to rearrange the data so that it will be automatically charted ever
time, via links to Sheet2.

Sheet1 layout below.

Col A Col E

Header1
Data1 Other Data1 in Col E

Header2
Data2 Other Data2 in Col E

Header3
Data3 Other Data3 in Col E

Header4
Data4 Other Data4 in Col E

Thanks for your expert help.

 

[Pete_UK]

Your example shows the data regularly spaced. Are you saying that
there may be more than one blank row between each segment of data, and
that this varies each time you import the report? Does the data Header
1 always appear in the same (starting) cell?

Pete

 

[Rick]

Thank you for your prompt reply.

The data is not regularly spaced between the Header and the Data.

There may be more than 1 blank row between each segment of data. It may, or
may not vary between each report.

So far the Header1 has appeared in A53. This may, or may not, continue in
the future. I don't compose the incoming report, nor can I request
consistent formatting.

Header location on one report:

Header1 A53
Header2 A114
Header3 A174
Header4 A238

On the next report:

Header1 A53
Header2 A114
Header3 A174
Header4 A235

The location variance is in Header4.

The Headers may, or may not, vary on subsequent reports. However the text
of each Header remains the same from report to report.

Data location of one report:

Data1 A61:E85
Data2 A122:E146
Data3 A182:E206
Data4 A243:E267

Data location on the next report:

Data1 A61:E85
Data2 A122:E146
Data3 A182:E206
Data4 A246:E270

The location variance is in Data4.

These locations may, or may not, continue in the future. The size of each
Data block should stay the same.

Thanks once again for your prompt reply and expert help.

 

[Rick]

Hi Pete,

My explanation of the Header texts may not have been clear. So let me give
an example.

Header1 Nuts
Header2 Bolts
Header3 Widgets
Header4 Gigets

The text for the 4 Headers will always be the same, but the Headers are not
identically named. That is, Header1 will always say Nuts on every report,
but never Bolts or any of the other 3 Header names.

I hope this is a better explanation of the Headers text.

This was why I was trying to key off of the Headers, as they remain the same
from report to report. Even though their row location may vary. So I tried
doing a Search and every other thing I could think of in Col A, keying off of
the Header names. I have not been able to come up with a solution. I was
hoping you or someone else would.

Thanks for your expert help.

 

[Pete_UK]

Perhaps you can insert a simple sequence in column F, 1, 2, 3, etc -
this will enable you to get the data back into the correct sequence
afterwards. Then you can sort the data on column E, so that all the
blanks go to the end of the block of data, and then sort just the
block of data using column F.

Does this get you nearer to what you want?

Hope this helps.

Pete

 

[Rick]

Hi Pete,

I don't need to sort the data as it is already in the correct order.

If I only had to do one chart per month, this solution might work. But I'm
going to have to do hundreds of these charts every month. So I can't afford
to spend any time sorting, unsorting and then tweaking with blank cells.

Let me restate what I want in a different manner, and perhaps it will become
clearer.

1. A report comes in and I copy/paste it into Excel on Sheet1.

2. I want to make a chart of the data.

3. There are 4 unique Headers, below each Header is a block of data, for 4
blocks of data.

4. I want to bring the 4 Headers and the 4 blocks of data over to Sheet2.

5. For starters, let's just bring over the 4 Headers, and forget about the
Data for now. I want to see if it can be done at all.

Step 1:

1. Header1 is in column A. The unique text in it is "Nuts".
2. Have Header1 come over to Sheet2 A53, via formula/function.
3. Header2 is in column A. The unique text in it is "Bolts".
4. Have Header2 come over to Sheet2 A114, via formula/function.
5. Header3 is in column A. The unique text in it is "Widgets".
6. Have Header3 come over to Sheet2 A174, via formula/function.
7. Header4 is in column A. The unique text in it is "Gigets".
8. Have Header4 come over to Sheet2 A238, via formula/function.

We do not know the particular cells the Headers will be in, only that they
have unique text, and that they are in column A.

By "unique text" I mean that no other cells in column A have this text.

Let's see if this can be done first, before attempting to bring over the
data. I want to know if it is even possible in Excel. I have not been able
to do it.

Thanks for your expert help and time spent on this.

 

[Pete_UK]

You can set up a master file for this, with a blank Sheet1 and in
Sheet2 you can just put your headings in the appropriate cells, i.e.:

A53: Nuts
A114: Bolts
A174: Widgets
A238: Gigets

Then in B53 you can have this formula:

=IF(ISNA(MATCH(A53,Sheet1!A:A,0)),"",MATCH(A53,Sheet1!A:A,0))

It will return the row on Sheet1 where that heading appears (or blank
if it is not there). You could use a white foreground colour for this
so that it is not seen. Then you can copy this into the cells B114,
B174 and B238.

Then to extract the data from column E you can make reference to this
row like this in E54:

=IF($B$53="","",IF(INDEX(Sheet1!E:E,$B$53+ROW(A1))="","",INDEX(Sheet1!
E:E,$B$53+ROW(A1))))

which is not volatile (unlike INDIRECT or OFFSET, which could also be
used).

Then this could be copied down to E113.

In E115 you could have this formula:

=IF($B$114="","",IF(INDEX(Sheet1!E:E,$B$114+ROW(A1))="","",INDEX
(Sheet1!E:E,$B$114+ROW(A1))))

which can then be copied down to E173. Note that this formula is
looking at B114, i.e the cell that matches with the second heading,
and that the ROW parameter starts with A1 again. You can then put
these formulae in B and B for the other two headings:

=IF($B$174="","",IF(INDEX(Sheet1!E:E,$B$174+ROW(A1))="","",INDEX
(Sheet1!E:E,$B$174+ROW(A1))))

=IF($B$238="","",IF(INDEX(Sheet1!E:E,$B$238+ROW(A1))="","",INDEX
(Sheet1!E:E,$B$238+ROW(A1))))

and copy these down as far as required. Of course, if you need to get
data from other columns you can just copy these formulae across as
required, keeping them within the blocks.

Save this as your "master" file, which is what you start with each
time you want to work with a new set of data. You would copy the new
data into Sheet1 of the master file, and then you can use File | Save
As to save this with a new name (thus the master file does not change,
and can be used next time). If you want, you can highlight columns A
to E of Sheet2 then click <copy>, then Edit | Paste Special | Values |
OK then <enter>, and this will fix the values, so that you could then
delete Sheet1 and save the new file once more.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, this bit doesn't make sense:

"...
You can then put these formulae in B and B for the other two headings:
...."

It should say:

You can then put these formulae in E175 and E239 for the other two
headings:

Hope this helps.

Pete