+ Reply to Thread
Results 1 to 12 of 12

Copy column without repeated number?

  1. #1
    Registered User
    Join Date
    12-18-2007
    Posts
    22

    Copy column without repeated number?

    how do i copy one column to another one (with formula) that not repeat any number?

    i have a list of prices and i need that list in other column with any price repeated.... and theses prices are imported data, so they change, thats way need to be formula =/



    shd

  2. #2
    Forum Contributor
    Join Date
    08-10-2006
    Posts
    723
    hi,

    can you post of an example of what you need
    steve

  3. #3
    Registered User
    Join Date
    12-18-2007
    Posts
    22
    ok, here it go

    1 Bradesco 100,00 17,31
    2 S. Barros 4.000,00 17,31
    3 Gradual 300,00 17,30
    4 Novinvest 700,00 17,30
    5 HSBC 500,00 17,30
    6 Ativa 200,00 17,30
    7 Intra 6.000,00 17,27
    8 Am.Invest 3.000,00 17,27
    9 Titulo 1.400,00 17,26
    10 Intra 100,00 17,25
    11 B-Primus 200,00 17,25
    12 Bradesco 200,00 17,25
    13 Bradesco 400,00 17,21
    14 S. Barros 1.800,00 17,21
    15 Bradesco 300,00 17,20
    16 Itau 500,00 17,20
    17 Bradesco 100,00 17,20




    this is on the sheet 1... its a imported data



    what i need on sheet 2, its a column with the price, only one time, and the quantity added to the same prices....

    for example, with the values would stay like this:

    4.100,00 17,31
    1.700,00 17,30
    9.000,00 17,27
    1.400,00 17,26
    500,00 17,25
    2.200,00 17,21
    900,00 17,20


  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    If the data on sheet1 is in the range A1:C17 then on sheet2:

    A1: =IF(B1="","",SUMPRODUCT(--(Sheet1!$C$1:$C$17=Sheet2!B1),(Sheet1!$B$1:$B$17)))
    B1: =IF(ROW()>SUM(1/COUNTIF(Sheet1!$C$1:$C$17,Sheet1!$C$1:$C$17)),"",INDEX(Sheet1!C:C,SMALL(IF(ISNUMBER(MATCH(ROW(Sheet1!$C$1:Sheet1!$C$17),MATCH(Sheet1!$C$1:Sheet1!$C$17,Sheet1!$C$1:Sheet1!$C$17,0),0)),ROW(Sheet1!$C$1:Sheet1!$C$17),""),ROW())))
    The formula in B1 is array entered. Hold down the Shift and CTRL keys, then press enter.

    Copy A1:B1 and paste down to A17.

    HTH

    rylo

  5. #5
    Registered User
    Join Date
    12-18-2007
    Posts
    22
    Quote Originally Posted by rylo
    Hi

    If the data on sheet1 is in the range A1:C17 then on sheet2:

    A1: =IF(B1="","",SUMPRODUCT(--(Sheet1!$C$1:$C$17=Sheet2!B1),(Sheet1!$B$1:$B$17)))
    B1: =IF(ROW()>SUM(1/COUNTIF(Sheet1!$C$1:$C$17,Sheet1!$C$1:$C$17)),"",INDEX(Sheet1!C:C,SMALL(IF(ISNUMBER(MATCH(ROW(Sheet1!$C$1:Sheet1!$C$17),MATCH(Sheet1!$C$1:Sheet1!$C$17,Sheet1!$C$1:Sheet1!$C$17,0),0)),ROW(Sheet1!$C$1:Sheet1!$C$17),""),ROW())))
    The formula in B1 is array entered. Hold down the Shift and CTRL keys, then press enter.

    Copy A1:B1 and paste down to A17.

    HTH

    rylo
    Can you explain me that?

    i didnt understand very well and i have to translate all the formula, because my office is in portuguese =p

  6. #6
    Forum Contributor
    Join Date
    08-10-2006
    Posts
    723
    hi rylo,

    i have put this into a file but the formulars do not seem to be working

    steve
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Steve

    I don't know where you are located, but I had to reformat the data so that it used english translations of the numbers. If you perform that change, it should work.

    shg
    I'm at a bit of a loss here as I don't know what the implications are if you try to translate to another language.

    Perhaps raise a new question to see if anyone can perform the translation you require.

    rylo

  8. #8
    Registered User
    Join Date
    12-18-2007
    Posts
    22
    can you make with this archive?

    New Arquivo ZIP do WinRAR.zip

  9. #9
    Forum Contributor
    Join Date
    08-10-2006
    Posts
    723
    hi rylo,

    I am in england. I copied the data from the post and put it into the speadsheet. then seperated it out into the columns. this was to try and help
    shd. but i could not get it to work
    steve
    Last edited by stevekirk; 01-30-2008 at 10:21 PM.

  10. #10
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Here 'tis.

    rylo
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-18-2007
    Posts
    22
    thx rylo, that is exactly what i need

    can you make me one more favor? do with the "venda" column too..

    i tried changing the columns, but didn't have to much success

  12. #12
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    I've updated the formulas but only put into some of the output range. Given the amount of data and the type of formulas, beware that it will take a long time to process.

    rylo
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1