Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search and export multiple records | Excel Worksheet Functions | |||
Top 90% of records - Bring out number | Excel Discussion (Misc queries) | |||
display set of records in a form with previous and next button | New Users to Excel | |||
how do i delete multiple records from my database | Excel Discussion (Misc queries) | |||
Counting Unique Records with multiple conditions | Excel Worksheet Functions |