Vlookup issue - Retail Environment

DPChristman

Board Regular
Joined
Sep 4, 2012
Messages
177
Office Version
  1. 365
Platform
  1. Windows
I am currently trying to link data between a large data file and a fill in form that I have created.

The source data is coming from an excel file that is over 34 MB and more that 177,000 lines.

This data deals with inventory shrinkage.

There are 7 columns that I am accessing on the source data file:
  • Store Number
  • Dept
  • Dept Name
  • Class
  • Class Name
  • Shrink Units
  • Shrink $

What I need to do is RANK the top five items (either Class or Department) for each store, and have them auto-filled into the form I have created.

Example, if the person enters the store number (ex: 123), the section of the form for top 5 shrinkage departments would populate with the following[TABLE="width: 217"]
<tbody>[TR]
[TD="class: xl65, width: 180"][/TD]
[/TR]
</tbody>[/TABLE]
DEPT DEPT NAME UNITS DOLLARS
JLY JEWELRY 318 $41,000
HAC HOME ACC 300 $25,000
GLS GLASSWARE 248 $18,000
WCH WATCHES 168 $15,000
FRG FRAGRANCE 151 $13,400



Since the file with the source data will not be open at the time that the form is filled out, using a sumifs formula really won't work.

Currently, my field people are doing a very awkward copy and paste function, and I am looking to simplify the process.

Anyone have any ideas how to accomplish this?

Thanks,

Dan
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,223,981
Messages
6,175,767
Members
452,668
Latest member
mrider123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top