Posts by wrigst

    Hi All,

    Firstly thanks in advance for the free advice - its much appreciated. So I have a main list of made up of names, Teams and Other text. The list is 100 rows but may not always be fully populated (e.g. sometimes it have 50 rows, the following month it may have 100), so some rows could be blank. The maximum population of this list will never exceed 100. In a separate list I want to retrieve 3 random names from the main list.


    I've tried this formula without success: =INDEX($D:$D,INDEX(MODE.MULT(IF($D$8:$D$100<>{"",""},ROW($D$8:$D$100))),RANDBETWEEN(1,SUM(N(LEN($D$8:$D$100)>0)))))


    Where D is the column in the main list with Names. Problem: This formula quite often produces duplicates, or blank cells.


    Not sure where I am going wrong with this one. I have attached screenshots of how the lists are made up if that helps.


    Happy to provide more info if needed. Thanks!

    Re: Lookup cell values from one column in another and delete


    Thanks Mumps - that worked a treat and was just what I was after.
    It does finish on a "Type Mismatch" error though and points to the line "Set foundVal = Range("A:A").Find(rng, LookIn:=xlValues, lookat:=xlWhole)"...?
    Almost as if it doesn't know how to end? Not that I am that bothered though, as the list is how I want it now.
    Thanks!

    Hi,


    First post and yes I am 80% excel noob.


    I think this is fairly simple, but my expertise in VB is very limited. I want to create a macro to lookup a value from Col. D in Col. A, and delete the entry found in Col. A. I have a functioning Vlookup which has found the entries, but I want to convert this to macro, with the added feature of deleting the cell where a match is found. Is this possible please?


    I have tried looking through the forum and on Google, but I am pretty sure I am searching using the wrong terms....


    Thanks in advance