Multiple records per row
 

I would like to use the autofilter function on an imported database.
Unfortunately I don't have the ability to alter the database until after I
import it into Excel. The problem I have is that some of the columns have
more than one record per excel row. For example:

1 2 3
---------------
a b c
d
---------------
e b g
---------------
a b c
b
b
----------------
s f h
---------------

The '----" denote excel rows. The "1,2,3" denote excel column headers. Any
ideas on how to proceed?
--
Regards, timg.

Multiple records per row
 

Hi

It looks like there are line feeds in the data, and format is et to wrap
text.

Try Find and Replace (Ctrl +H)
Find Char(10)
Replace leave blank

If that doesn't work, in a spare column try
=SUBSTITUTE(A1,Char(10),"")
and see if your
b
b
b
becomes bbb

If so, repeat this for all three columns and copy down, then copy the 3
columns and Paste SpecialValues to "fix" the data.
Carry out your filter on this revised set of data.

--
Regards

Roger Govier


"timg" wrote in message
...
I would like to use the autofilter function on an imported database.
Unfortunately I don't have the ability to alter the database until
after I
import it into Excel. The problem I have is that some of the columns
have
more than one record per excel row. For example:

1 2 3
---------------
a b c
d
---------------
e b g
---------------
a b c
b
b
----------------
s f h
---------------

The '----" denote excel rows. The "1,2,3" denote excel column
headers. Any
ideas on how to proceed?
--
Regards, timg.

Multiple records per row
 

Roger,

Thank you for your answer. However, I was not clear in my goal. Referring
to the example I gave, I show the first row (and header) as:

1 2 3
----------------------
a b c
d
----------------------

I want to convert this row into 2 rows:

1 2 3
----------------------
a b c
----------------------
a b d
----------------------

And, for example, again referring the earlier example, I'd like the third
row converted into 3 rows in a similar way as to what I just showed above. I
want to do whatever the solution is to all the rows. Then I can apply the
autofilter, sort, or whatever.
--
Regards, timg.


"Roger Govier" wrote:

Hi

It looks like there are line feeds in the data, and format is et to wrap
text.

Try Find and Replace (Ctrl +H)
Find Char(10)
Replace leave blank

If that doesn't work, in a spare column try
=SUBSTITUTE(A1,Char(10),"")
and see if your
b
b
b
becomes bbb

If so, repeat this for all three columns and copy down, then copy the 3
columns and Paste SpecialValues to "fix" the data.
Carry out your filter on this revised set of data.

--
Regards

Roger Govier


"timg" wrote in message
...
I would like to use the autofilter function on an imported database.
Unfortunately I don't have the ability to alter the database until
after I
import it into Excel. The problem I have is that some of the columns
have
more than one record per excel row. For example:

1 2 3
---------------
a b c
d
---------------
e b g
---------------
a b c
b
b
----------------
s f h
---------------

The '----" denote excel rows. The "1,2,3" denote excel column
headers. Any
ideas on how to proceed?
--
Regards, timg.

Multiple records per row
 

To get the data to look like your example...............

Select columns A and B

F5SpecialBlanksOK

Type an = sign into the active cell then point to the cell above and hit CTRL +
ENTER keys together.

When happy copy the range then paste specialvaluesokesc to get rid of
formulas.


Gord Dibben MS Excel MVP

On Wed, 20 Sep 2006 10:32:01 -0700, timg wrote:

Roger,

Thank you for your answer. However, I was not clear in my goal. Referring
to the example I gave, I show the first row (and header) as:

1 2 3
----------------------
a b c
d
----------------------

I want to convert this row into 2 rows:

1 2 3
----------------------
a b c
----------------------
a b d
----------------------

And, for example, again referring the earlier example, I'd like the third
row converted into 3 rows in a similar way as to what I just showed above. I
want to do whatever the solution is to all the rows. Then I can apply the
autofilter, sort, or whatever.

Multiple records per row
 

Gord,

Thank you for your help.
--
Regards, timg.


"Gord Dibben" wrote:

To get the data to look like your example...............

Select columns A and B

F5SpecialBlanksOK

Type an = sign into the active cell then point to the cell above and hit CTRL +
ENTER keys together.

When happy copy the range then paste specialvaluesokesc to get rid of
formulas.


Gord Dibben MS Excel MVP

On Wed, 20 Sep 2006 10:32:01 -0700, timg wrote:

Roger,

Thank you for your answer. However, I was not clear in my goal. Referring
to the example I gave, I show the first row (and header) as:

1 2 3
----------------------
a b c
d
----------------------

I want to convert this row into 2 rows:

1 2 3
----------------------
a b c
----------------------
a b d
----------------------

And, for example, again referring the earlier example, I'd like the third
row converted into 3 rows in a similar way as to what I just showed above. I
want to do whatever the solution is to all the rows. Then I can apply the
autofilter, sort, or whatever.