Multiple records per row

T

timg

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
 
R

Roger Govier

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 Special>Values to "fix" the data.
Carry out your filter on this revised set of data.
 
T

timg

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.
 
G

Gord Dibben

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

Select columns A and B

F5>Special>Blanks>OK

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 special>values>ok>esc to get rid of
formulas.


Gord Dibben MS Excel MVP
 
Top