Announcement

Collapse
No announcement yet.

GBP £100 - Script to cycle through data set, Search and output to txt file.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • GBP £100 - Script to cycle through data set, Search and output to txt file.



    Hi All,

    (10% (£10 paid to [email protected])

    I have a set of data in Column A.

    I would like to run a script which searches the first value in column A (example shows I want to search for "A" shown in cell "A2".

    So it searches for the "A" and finds it in cell A2 and outputs the value of the cell below it, in this case "B".

    The script then continues to search for the next "B" and returns the value below that, i.e. "F", it then searches for the next "F" giving an output of "T" and so on.

    So the script would cycle through the input data (and start from that point) and output the values in a new column.

    Input Data Output for value of A2 Output for value of A3 Output for value of A4 Output for value of A5 Output for value of A6
    A B C D A B
    B F F
    C T
    D P
    A
    B
    F
    G
    A
    F
    T
    A
    E
    T
    P
    But, Because the number of data lines in column A could be thousands upon thousands, this will produce too many columns for excel to handle, so instead of outputting into excel, could this be outputted into a txt file.

    The other issue is that Column A may contain empty rows, so the script would need to somehow ignore the blank rows.

    So the txt file output would read like this (as if it concatenated the output adding a space between each result:

    B F T P
    C
    D
    A F
    B

  • #2
    Hi FrazzleDazzle,

    I can look at this.

    Some questions:

    Do you have a file with sample data I could use?
    How many rows will there be in your file?
    Which version of Excel do you use?

    GC

    Comment


    • #3
      Hi GC,

      Sample file attached.

      How many rows will there be in your file? Could be up to 100000 (or more) (Hence why I would like a txt output as excel wont be able to produce that many columns)
      Which version of Excel do you use? 2016
      Attached Files

      Comment


      • #4
        Hi,
        Thanks for the sample file.

        I assume the data will not be simple letters... can you be a little more specific?
        With 100000 rows, size of data can have some importance.

        Thanks

        Comment


        • #5
          Hi GC,

          Each row of data would be no more than 12 characters.

          I have attached a modified file and a sample txt file output

          hope this helps?

          Thanks
          Attached Files

          Comment


          • #6
            Hi,

            One more question. How many "UNIQUE" values will you have?

            On the 100000 rows of data, will you have 5000, 10000, 50000 unique values?
            Again, this has an impact on the solution and processing time.

            Thanks

            Comment


            • #7
              Hi, I would estimate that there would be approx. 50% unique values. so 50000

              Comment


              • #8
                Ok - I have a solution ready for you.
                I will send you my payment details via PrivateMessage and I will send you the code once payment is received.

                With fake data (100000 rows with about 50% unique values, it takes 5-6 seconds to run on my PC)
                GC

                Comment


                • #9
                  Hi GC,

                  Payment 0f £90 sent, can you send to my email address please (added to the notes on paypal)

                  Comment


                  • #10
                    Payment received and file sent to OP.
                    Thanks.

                    Comment


                    • #11
                      Haha!,

                      Ok, that was pretty cool, that code was absolutely spot on!

                      Great job GC!

                      Comment


                      • #12


                        Thanks for the feedback !

                        Comment

                        Working...
                        X