Hi everyone,

i guess its easier to explain with an example.

I have:

A1 acb
A2
A3 fff
A4
A5
A6
A7 fre
A8


I want:

B1 acb
B2 fff
B3 fre


Best Regards to everyone

One way which makes it dynamic to source data in col A

In B1:
=IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) ))

In C1:
=IF(A1="","",ROW())

Select B1:C1, copy down to cover the max expected extent of data in col A.
Hide away col C. Col B will return the required results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vincent" wrote:
Hi everyone,

i guess its easier to explain with an example.

I have:

A1 acb
A2
A3 fff
A4
A5
A6
A7 fre
A8


I want:

B1 acb
B2 fff
B3 fre


Best Regards to everyone

Thanks for answering max

However it didn´t help me. Once again ill explain with an example related to
the last one:

My result with
=IF(ROW()COUNT(C1:C100),"",INDEX(A1:A100,SMALL(C1 :C100,ROW()))):

B1 0
B2 fff
B3 0
B4 0
B5 fre
B6

My goal:

B1 acb
B2 fff
B3 fre
B4
B5


"Max" escreveu:

One way which makes it dynamic to source data in col A

In B1:
=IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) ))

In C1:
=IF(A1="","",ROW())

Select B1:C1, copy down to cover the max expected extent of data in col A.
Hide away col C. Col B will return the required results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vincent" wrote:
Hi everyone,

i guess its easier to explain with an example.

I have:

A1 acb
A2
A3 fff
A4
A5
A6
A7 fre
A8


I want:

B1 acb
B2 fff
B3 fre


Best Regards to everyone

My result with
=IF(ROW()COUNT(C1:C100),"",INDEX(A1:A100,SMALL(C1 :C100,ROW()))):

Hey, but that's not what I posted. You modified it, wrongly.

If for some reason you can't use entire col references (which are simpler,
really), then use this correct version of your modification above instead as
the formula in B1:
=IF(ROW()COUNT(C$1:C$100),"",INDEX(A$1:A$100,SMAL L(C$1:C$100,ROW())))
Copy B1 down. That will return exactly the results that you seek in col B.

I of course, presume that you have the criteria formula suggested earlier in
C1 down intact.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vincent" wrote:
Thanks for answering max

However it didn´t help me. Once again ill explain with an example related to
the last one:

My result with
=IF(ROW()COUNT(C1:C100),"",INDEX(A1:A100,SMALL(C1 :C100,ROW()))):

B1 0
B2 fff
B3 0
B4 0
B5 fre
B6

My goal:

B1 acb
B2 fff
B3 fre
B4
B5


"Max" escreveu:

One way which makes it dynamic to source data in col A

In B1:
=IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) ))

In C1:
=IF(A1="","",ROW())

Select B1:C1, copy down to cover the max expected extent of data in col A.
Hide away col C. Col B will return the required results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vincent" wrote:
Hi everyone,

i guess its easier to explain with an example.

I have:

A1 acb
A2
A3 fff
A4
A5
A6
A7 fre
A8


I want:

B1 acb
B2 fff
B3 fre


Best Regards to everyone

Max i´m really sorry. I fell like a newbie. I translated wrongly row to
portuguese. So in English i had:

=IF(column()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,col umn()))):

Thanks a lot for your help

Best regards


"Max" escreveu:

My result with
=IF(ROW()COUNT(C1:C100),"",INDEX(A1:A100,SMALL(C1 :C100,ROW()))):

Hey, but that's not what I posted. You modified it, wrongly.

If for some reason you can't use entire col references (which are simpler,
really), then use this correct version of your modification above instead as
the formula in B1:
=IF(ROW()COUNT(C$1:C$100),"",INDEX(A$1:A$100,SMAL L(C$1:C$100,ROW())))
Copy B1 down. That will return exactly the results that you seek in col B.

I of course, presume that you have the criteria formula suggested earlier in
C1 down intact.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vincent" wrote:
Thanks for answering max

However it didn´t help me. Once again ill explain with an example related to
the last one:

My result with
=IF(ROW()COUNT(C1:C100),"",INDEX(A1:A100,SMALL(C1 :C100,ROW()))):

B1 0
B2 fff
B3 0
B4 0
B5 fre
B6

My goal:

B1 acb
B2 fff
B3 fre
B4
B5


"Max" escreveu:

One way which makes it dynamic to source data in col A

In B1:
=IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) ))

In C1:
=IF(A1="","",ROW())

Select B1:C1, copy down to cover the max expected extent of data in col A.
Hide away col C. Col B will return the required results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vincent" wrote:
Hi everyone,

i guess its easier to explain with an example.

I have:

A1 acb
A2
A3 fff
A4
A5
A6
A7 fre
A8


I want:

B1 acb
B2 fff
B3 fre


Best Regards to everyone

No prob, you're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vincent" wrote in message
...
Max i´m really sorry. I fell like a newbie. I translated wrongly row to
portuguese. So in English i had:

=IF(column()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,col umn()))):

Thanks a lot for your help

Best regards

Copy column A into column B, then highlight from B1 down to the last
entry in column B. Press F5 (or Edit | GoTo), click Special and click
Blanks then OK. Use Edit | Delete... then Shift Cells Up and OK, to
get what you want.

Hope this helps.

Pete

On Sep 26, 3:02 pm, Vincent wrote:
Hi everyone,

i guess its easier to explain with an example.

I have:

A1 acb
A2
A3 fff
A4
A5
A6
A7 fre
A8

I want:

B1 acb
B2 fff
B3 fre

Best Regards to everyone

Thanks peter for your help.

However, i have a lot of data which means i dont want to do it manually.

Besides, i could just shift copy every cell i was interested in and then
just paste. They would come out OK just the way i wanted. But once again, i
don´t to do it manually.

Best Regards

"Pete_UK" escreveu:

Copy column A into column B, then highlight from B1 down to the last
entry in column B. Press F5 (or Edit | GoTo), click Special and click
Blanks then OK. Use Edit | Delete... then Shift Cells Up and OK, to
get what you want.

Hope this helps.

Pete

On Sep 26, 3:02 pm, Vincent wrote:
Hi everyone,

i guess its easier to explain with an example.

I have:

A1 acb
A2
A3 fff
A4
A5
A6
A7 fre
A8

I want:

B1 acb
B2 fff
B3 fre

Best Regards to everyone

Vincent wrote:
Thanks peter for your help.

However, i have a lot of data which means i dont want to do it manually.

Besides, i could just shift copy every cell i was interested in and then
just paste. They would come out OK just the way i wanted. But once again, i
don´t to do it manually.

Best Regards

"Pete_UK" escreveu:

Copy column A into column B, then highlight from B1 down to the last
entry in column B. Press F5 (or Edit | GoTo), click Special and click
Blanks then OK. Use Edit | Delete... then Shift Cells Up and OK, to
get what you want.

Hope this helps.

Pete

On Sep 26, 3:02 pm, Vincent wrote:
Hi everyone,

i guess its easier to explain with an example.

I have:

A1 acb
A2
A3 fff
A4
A5
A6
A7 fre
A8

I want:

B1 acb
B2 fff
B3 fre

Best Regards to everyone


Could you just auto filter column A for non blanks and then copy and
paste to column B. Or record a macro to do it for you?
ps Im no expert.

I must say i wasn´t expecting this much help from so many people.

Thanks everyone.

Anyway, every single explanation was correct. However i was looking for a
formula like max´s.

Thanks Lynz