Transpose Column With Gaps to Column With no gaps

[Guest]

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

 

[Guest]

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.

 

[Guest]

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:

 

[Pete_UK]

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

 

[Guest]

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:

 

[Guest]

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,SMALL(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.

---

 

[Guest]

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,column()))):

Thanks a lot for your help

Best regards


"Max" escreveu:

 

[Max]

No prob, you're welcome.

 

[Lynz]

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:

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.

 

[Guest]

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