INDEX/Match help (VBA eventually)

[Buzz1126]

I'm posting this in the VBA section because ultimately I will use VBA to do this but I believe if someone could just help me with a normal function I can translate it to a VBA "For" statement myself.

A user enters a number in the message box and this value is stored in AuctionNum. I believe the number is entered into the table as type general.

Please Login or Register  to view this content.

I then need to use this number to look up several sets of data within a table. The AuctionNum occurs in column C, but there are several different AuctionNums within the table. So anytime the entered AuctionNum occurs, I need to pull data from the corresponding row. The main data I need is the WorkOrder Number which occurs in column A. I also need to grab the Car Year (col G), Car Make (col H), the seller( col E), and the body (col L).

I set up a table to place the values that are pulled. So every data point in this new table will have an AuctionNum of whatever number is entered by the user.

Auction Num WorkOrder Car Year Car Make Seller Body

Please Login or Register  to view this content.

Placing this data in a 2-D array would help me as well because eventually I need an array with the WO number in column 1 and the Body Type in column 2.

Here's the formula (not code) I tried to use but it doesn't work. I think it just grabs every single WO number from column A

Please Login or Register  to view this content.

B3 contains the correct auction number (i.e. if there are 50 cars to be sold in auction 39, then 39 is pasted 50x in the column) and the data table with all of the auction data is titled 'Mannheim Car Data'

I feel like I started to ramble because I wanted it to make sense but i'll be on my computer if there's any questions.

Thanks for the help,

Matt

[Leith Ross]

Hello Matt,

You should post a copy of your workbook.

[Buzz1126]

Ok here is my file.

Edit:

Hit the run button Daily Auction sheet tab, type in 39. Next I am pulling all of the data from the rows that contain auction 39 from the Mannheim Auction Data tab. Then, I am filling in the Current Auction Database with the pulled data.

[Tony Valko]

Similar thread in the Formulas/Functions forum...

https://www.excelforum.com/showthread.php?t=1164682

[Buzz1126]

That's me also! Posted it in the other place to get help with excel in general. I then posted it here too to get help with the VBA part of it.

So here is the question simplified,

I need to grab all the WO (in col. A) that have an auction number of 39 (in col. C) and place the values in an array. Just a simple list of the WO that are going to be in auction 39.

[SPARKY347]

just taking a quick look at this, where is the input box or where would you like it to be for the user to input the sale# into to start and retrieve all the data? also is all this data supposed to be shown on the current data sheet?

[SPARKY347]

hahah oops I didn't read the above post that cleared up all my questions

[mike7952]

A lot different approach but here is my example of filling in the Current Auction Data Sheet

Please Login or Register  to view this content.

[SPARKY347]

I did this using an advanced filter but for some reason the site will not allow me to upload it so heres the code, you will have to change on work sheet "currentauction", swap columns G and H around

Please Login or Register  to view this content.

[Buzz1126]

Thank you both so much for the help. Sorry I'm late getting back to y'all as it was my birthday yesterday (and I'm in college haha). Running through everything now.

Matt

[SPARKY347]

If any of our posts helped you please mark as solvef