I give up, VBA it is, But there is a twist..

[Baba_Yaga]

So I have been trying to do a supercharged vlookup. where it could match a part number on one sheet, with the one on another sheet ( after both have been trim and matched in format ) and return all the drop locations. Well the array that worked the best, crashed Excel and would run too long. So VBA should work.

Down side is, I don't know jack about VBA, is there a way to get a prompted Macro/VBA
that would prompt you for columns and you want to return?

Example:
I want to match Sheet 1 A2 with newbook,sheet3 b11 return aa2 (Location) an matching ab2 (qty)

 

[Special-K99]

I can think of several ways of doing this, some of which will be wrong.
I dont want to waste time on the wrong solutions so you need to be specific.


Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[Baba_Yaga]

https://cdn1.imggmi.com/uploads/2018/8/6/aa42f79578d8ca9f7a712bc00cacc152-full.png

As you shold be able to see in the image,
the sheet with multiple instances is where i want to get the information FROM we will call this sheet 1100
and the sheet with one instance if whrere i need to to go to. we will call this sheet 5100
Bliss would be a a prompted macro that would let me select columns because
the column on 1100's sheet will 99% of the time be the same, how ever 5100's sheet willmove to the right most of the time, rareley to the left
now getting the Deliveyr location from 1100 to 5100 is the basic goal, then i would have to go back and check the part qty from 1100 and add it to 5100 sheet under the blue( like I said before there are 1-36 of the blue columns depending on the model.
with that being said, and idea where to get started?

 

[Special-K99]

Looks like VBA would be the best choice, unfortunately I'm not an expert in that.

At least you've got a good decription of what you want to do for anyone that can tackle this in VBA.

 

[Baba_Yaga]

I believe I came close in index array but it would always crash excel

 

[Special-K99]

Your cdn1 link says No Image, though Im not a VBA expert so I'm not gonna be much help on this.
You're better off posting a spreadsheet than an image btw so people can edit and test their solutions, cant do that with an image.