What I am trying to do is build a series of formulas, commands, and or a macro that works with them to take the latest entry in a list which is expanded daily, paste four out of seven columns, rearrange those columns to work with a vlookup, have it only select the most recent, and then output two of those columns together in the same cell without spaces so I can copy and paste it into a search window.
What I have:
A Vlookup with a wildcard that takes a unit number and displays the quote that it was for.
What I want:
I would like a method or macro that takes 4 columns (out of 7) from a protected shared workbook and places it in another spreadsheet. These three columns would need to be rearranged to work with my vlookup macro. So if the order was 1,2,3,4,5,6,7 I need 6,5,1,4 in that order (the other columns don't matter).
Specifically I have one spreadsheet that grows everyday that is protected and shared. Manually taking out the quotes and rearranging them takes too long. I also want to add a formula into the vlookup or another lookup method altogether to take the most recent result.
So for example if I have the quote 12885 & 12895 for the same unit I want it to show 12895 and not 12885
Another way to explain it
Quote:12895 - is for unit "Apple" <---- I want this one because it is more recent
Quote:12885 - is for unit "Apple"
My quote numbers are perpetually ascending so I'm wondering if I can use a max or something? I'm having difficulty thinking it through and I'm not sure if it is possible to nest that function into the vlookup.
The last thing I'm looking to do is have it output a combined searchable line of text. I realize I can do part of it with a concatenate formula but I need no spaces as the output. So for example see below (the periods are only there to represent spacing to try and make the idea clearer. I'm unsure how you just put a space in instead of a character. There would be No periods!
Column A.........,........B.............,.......... C ............ - Column A is the quote number - Column C is the company name
......Q6485 ....................................Joes peaches
I want this as a copyable line
.......Column A...................., B.............., .... C
.......JoespeachesQ6485
So in words I want it to take the quote number and put it behind the company name then paste it into a different cell and remove the spaces in the same cell. Effectively making it something I can search for specifically.
And no I can't search partial matches because of the way their computers are setup. I have no idea why but if I try and search the numbers at the end without the exact beginning characters it shows no results.
I can script kitty simple macros and I'm pretty good with the recorder. I am learning vba, albeit slowly. I have a piecemeal knowledge of excel and I'm in an environment right now where I need to focus on learning what I need. I have a pretty decent knowledge of formulas, I know of functions (used 2 that I didn't write), and I have put together a few useful lengthy macros piecing together tidbits off the internet
Any help would be greatly appreciated. And hey if you can only comment on one part of the problem I'm all ears!
Thanks guys!
Bookmarks