lookup and arrange data

[jimmy brasser]

The file is attached in a word document, but it comes (of course) fro
an excelsheet.

I want to organise the data like the example in the attache
file......

I appresiate your help!!!!!!!!!!!!!!!!!!!!!!

+----------------------------------------------------------------
| Attachment filename: xls.jpg
|Download attachment: http://www.excelforum.com/attachment.php?postid=372173
+----------------------------------------------------------------

 

[Lady Layla]

You will need to describe your problem in a message rather than attaching a
file.
With all the viruses that are roaming the internet, most people will not open an
attachment



:
: The file is attached in a word document, but it comes (of course) from
: an excelsheet.
:
: I want to organise the data like the example in the attached
: file......
:
: I appresiate your help!!!!!!!!!!!!!!!!!!!!!!!
:
:
: +----------------------------------------------------------------+
: | Attachment filename: xls.jpg |
: |Download attachment:
http://www.excelforum.com/attachment.php?postid=372173|
: +----------------------------------------------------------------+
:
: ------------------------------------------------
: [/url]
:
:
: ~~Now Available: Financial Statements.xls, a step by step guide to creating
financial statements

 

[jimmy brasser]

Okay Layla, thanks for the advise.

PANEL___NUMBER__SS001____X_____X_______SS002

18.4____________12/297464__A____A_______12/300613
________L53471__2/285165___D____D______2/297194
________________1/297466___C____C______1/303615
________________1/297468___C____C______1/303613
________________1/297470___C____C_______1/303611
________________1/297472___B____B_______1/300769
________________1/297474___C____C_______1/300749
________________1/297476___B____B_______1/300753
________________1/297720___C____C_______1/300615
________________1/297722___C____C_______1/297722
18.5_____________1/300614___A____A_______1/308805
________L53471__2/302301___D____D_______2/300747

The file is in another workbook and I want to it to be organised like
below in my own (workbook). The reference is 18.4. The numbers must be
filled till the next panel starts (18.5), because more data could be
added. Is there a way?


PANEL___NUMBER__SS001__________SS002

18.4____L53471__12/297464________12/300613
________________2/285165_________2/297194
________________1/297466_________1/303615
________________1/297468_________1/303613
________________1/297470_________1/303611
________________1/297472_________1/300769
________________1/297474_________1/300749
________________1/297476_________1/300753
________________1/297720_________1/300615
________________1/297722_________1/297722

 

[Dave Peterson]

Is your original data in 6 columns (A:F)?

And you want your final data to have the number (like L53471) moved to the row
above with the panel entered?

If yes, then you try this:

Copy your worksheet to a new workbook.
delete columns D:E
Add headers (if you don't have them already)
select A and then
data|filter|autofilter
Use the dropdown in Column A to show the non-blanks
Notice that some rows are hidden
(I'm gonna guess that row 2 is visible--modify this assumption to match your
table.)

Select B2 to the bottom of your data
type this in B2:
=B3
but hit ctrl-enter instead of just enter.
This'll fill the selection with formulas that will evaluate to the value in the
cell below.

Now Data|filter|Showall
select column B and do
edit|copy, edit|paste special|values
(to get rid of the formulas)

Now back to the filter and filter on column A.
But this time, show just the Blanks
select the stuff in column B and Edit|Clear|contents

Now Data|filter|Showall
to see if it's ok.

=======
If you really wanted the panel and number copied to each row (makes somethings
easier later), you can use the technique at Debra Dalgleish's site:

http://www.contextures.com/xlDataEntry02.html

(You still have to do that other stuff first, though.)