Same text in column as one

  • Thread starter inta251 via OfficeKB.com
  • Start date
I

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)
 
J

Joel

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
 
J

Joel

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

Joel

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

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
 
I

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)
 
J

Joel

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.

inta251 via OfficeKB.com said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top