Same text in column as one

[inta251 via OfficeKB.com]

I have sheet1 by name trips, where in column W I’ve companies’ names.
Same companies’ names can be infinity.
Sample:
Column W
Row2 Uuuuuuu
Row3 Aaaaaaa
Row4 Ccccccc
Row5 Ddddddd
Row6 Aaaaaaa
Row7 Zzzzzzz
Row8 Ccccccc
Row9 Ddddddd
Row10 Uuuuuuu
and so on.

Need formula in Sheet2 Column A from row2 .
Look for names in Sheet1 column W and put same company name as one text.

Sample:
Column A
Row2 Uuuuuuu
Row3 Aaaaaaa
Row4 Ccccccc
Row5 Ddddddd
Row6 Zzzzzzz
Row7 blank
Row8 blank
and so on
If no new companies name live blank

Similar to this I’ve formula for Dates
=IF(MAX(trips!A$2:A$1200)>MAX($A$3:A3),
MIN(INDEX((trips!A$2:A$1200>N(A3))*(trips!A$2:A$1200)
+(trips!A$2:A$1200<=N(A3))*10^9,0)),"")

Thanks in advance.
Sincerely, Igor (inta251)

 

[Guest]

This is pretty easy!

Put in A2
=A11

Put in A3 then copy to the rest of column A (not A1 or A2)

=IF(ISNUMBER(MATCH(W3,A$2:A2,0)),"",W3)

this will search for duplicates already in column A from A2 to A2

When copied to A5
=IF(ISNUMBER(MATCH(W5,A$2:A4,0)),"",W5)
which will check from a2 to A5 for a duplicate

 

[Guest]

The esiestt way is just to sort the data. don't know if this is what you are
looking for. Otherwise, a macro need to be written to remove the blanks.

 

[Guest]

Another thought. If you need to get the numbers back to the original order,
add column X witth the numbers 1,2,3, .. (use auto number). Sort on columns
w and X using X as the index. Delte the blank rows. Then sort using the
auto index column to get the data back in the original order.

 

[Rodrigo Ferreira]

Is it possible to order this list without blanks? e.g.:
Column A
Row1 Uuuuuuu
Row2 Aaaaaaa
Row3 Ccccccc
Row4 Ddddddd
Row5 Zzzzzzz
Row6
Row7
Row8
Row9
Row10

The result using your formula is:
Column A
Row1 Uuuuuuu
Row2 Aaaaaaa
Row3 Ccccccc
Row4 Ddddddd
Row5
Row6 Zzzzzzz
Row7
Row8
Row9
Row10

 

[inta251 via OfficeKB.com]

Thanks for respond.
This formula creating Circular: A2

This is pretty easy!

Put in A2
=A11

Put in A3 then copy to the rest of column A (not A1 or A2)

=IF(ISNUMBER(MATCH(W3,A$2:A2,0)),"",W3)

this will search for duplicates already in column A from A2 to A2

When copied to A5
=IF(ISNUMBER(MATCH(W5,A$2:A4,0)),"",W5)
which will check from a2 to A5 for a duplicate

I have sheet1 by name trips, where in column W I’ve companies’ names.
Same companies’ names can be infinity.

[quoted text clipped - 33 lines]

Thanks in advance.
Sincerely, Igor (inta251)

 

[Guest]

Your right. The sort won't work unless you convert the formula to values.
If you copy the original data containing the blank rows. The Paste special
(edit Menu) with selecting value. Then the formula will be replaced with
acttual values. The you can do the sort.

Thanks for respond.
This formula creating Circular: A2

This is pretty easy!

Put in A2
=A11

Put in A3 then copy to the rest of column A (not A1 or A2)

=IF(ISNUMBER(MATCH(W3,A$2:A2,0)),"",W3)

this will search for duplicates already in column A from A2 to A2

When copied to A5
=IF(ISNUMBER(MATCH(W5,A$2:A4,0)),"",W5)
which will check from a2 to A5 for a duplicate

I have sheet1 by name trips, where in column W I’ve companies’ names.
Same companies’ names can be infinity.

[quoted text clipped - 33 lines]

Thanks in advance.
Sincerely, Igor (inta251)