| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Same text in column as one
I have sheet1 by name trips, where in column W Ive 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 Ive formula for Dates =IF(MAX(trips!A$2:A$1200)MAX($A$3:A3), MIN(INDEX((trips!A$2:A$1200N(A3))*(trips!A$2:A$12 00) +(trips!A$2:A$1200<=N(A3))*10^9,0)),"") Thanks in advance. Sincerely, Igor (inta251) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same text in column as one
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 "inta251 via OfficeKB.com" wrote: I have sheet1 by name trips, where in column W Ive 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 Ive formula for Dates =IF(MAX(trips!A$2:A$1200)MAX($A$3:A3), MIN(INDEX((trips!A$2:A$1200N(A3))*(trips!A$2:A$12 00) +(trips!A$2:A$1200<=N(A3))*10^9,0)),"") Thanks in advance. Sincerely, Igor (inta251) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same text in column as one
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. "Rodrigo Ferreira" wrote: 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 -- Rodrigo Ferreira "Joel" escreveu na mensagem ... 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 "inta251 via OfficeKB.com" wrote: 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$1200N(A3))*(trips!A$2:A$12 00) +(trips!A$2:A$1200<=N(A3))*10^9,0)),"") Thanks in advance. Sincerely, Igor (inta251) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same text in column as one
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" wrote: 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 -- Rodrigo Ferreira "Joel" escreveu na mensagem ... 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 "inta251 via OfficeKB.com" wrote: 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$1200N(A3))*(trips!A$2:A$12 00) +(trips!A$2:A$1200<=N(A3))*10^9,0)),"") Thanks in advance. Sincerely, Igor (inta251) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same text in column as one
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 -- Rodrigo Ferreira "Joel" escreveu na mensagem ... 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 "inta251 via OfficeKB.com" wrote: 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$1200N(A3))*(trips!A$2:A$12 00) +(trips!A$2:A$1200<=N(A3))*10^9,0)),"") Thanks in advance. Sincerely, Igor (inta251) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200702/1 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same text in column as one
Thanks for respond.
This formula creating Circular: A2 Joel wrote: 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 Ive companies names. Same companies names can be infinity. [quoted text clipped - 33 lines] Thanks in advance. Sincerely, Igor (inta251) -- Message posted via http://www.officekb.com |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Same text in column as one
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" wrote: Thanks for respond. This formula creating Circular: A2 Joel wrote: 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 Ive companies names. Same companies names can be infinity. [quoted text clipped - 33 lines] Thanks in advance. Sincerely, Igor (inta251) -- Message posted via http://www.officekb.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF: 2 criteria: Date Range Column & Text Column | Excel Worksheet Functions | |||
| Counting a mixed text/number column based on text in another colum | Excel Discussion (Misc queries) | |||
| To copy values in a column relevant to text in an adjacent column? | Excel Worksheet Functions | |||
| Wrap text in column headers to fit text in column | Excel Discussion (Misc queries) | |||
| How I can print full text bigger than column, in repeat column | Excel Discussion (Misc queries) | |||
Linear Mode
