Lookup repeated entries return greatest value

L

lochem

New Member
Joined
Jun 14, 2010
Messages
28
hi all,
i have 2 sheets, one with data and one to lookup something from data sheet.
"SalesData" worksheet has repeated row entries (multiple identical orders).

i have copied the "itemnum" and "itemname" from SalesData into worksheet "inventory" and removed duplicates, then sorted by itemNum from least to greatest.
i want to place a column next to itemname, in worksheet inventory, titled "amount in stock"

there is a field in SalesData titled "amount in stock" with numbers only: for each order (i.e. row) there is a number displaying how many items (of a specific item) are left in stock after this order.
the numbers are simply numbers, not solved by any formula.

i want to lookup into the SalesData sheet,
find all instances of any item number (reference can be from my new list in "inventory" since i have no repeated item numbers there),
and
for all found instances of this item number, show me lowest number found in lookup column ("SalesData!"amount in stock")

how can i do this?? i have tried vlookup... not working.. is index and match the way to go?
thanks:confused:

PS ignore title, i want LOWEST number appearing
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
<HTML><HEAD><TITLE>New Page</TITLE>
****** http-equiv=Content-Type content="text/html; charset=windows-1252">
****** content="MSHTML 6.00.5730.13" name=GENERATOR></HEAD>
<BODY>
<P>this is my original data:</P>

<TABLE style="WIDTH: 196px; HEIGHT: 243px" cellSpacing=1 cellPadding=1 width=196 bgColor=#ffffff border=1>
<TBODY>
<TR>

<TD>
<P align=center><STRONG>A</STRONG></P></TD>
<TD>
<P align=center><STRONG>B</STRONG></P></TD></TR>
<TR>
<TD>
<P><STRONG>1</STRONG></P></TD>
<TD><STRONG>ItemNum</STRONG></TD>
<TD><STRONG>Amt In Stock</STRONG></TD></TR>
<TR>
<TD><STRONG>2</STRONG></TD>
<TD><STRONG>10102</STRONG></TD>
<TD><STRONG>14</STRONG></TD></TR>
<TR>
<TD><STRONG>3</STRONG></TD>
<TD><STRONG>10203</STRONG></TD>
<TD><STRONG>10</STRONG></TD></TR>
<TR>
<TD><STRONG>4</STRONG></TD>
<TD><STRONG>11502</STRONG></TD>
<TD><STRONG>19</STRONG></TD></TR>
<TR>
<TD><STRONG>5</STRONG></TD>
<TD><STRONG>10203</STRONG></TD>
<TD><STRONG>9</STRONG></TD></TR>
<TR>
<TD><STRONG>6</STRONG></TD>
<TD><STRONG>10406</STRONG></TD>
<TD><STRONG>21</STRONG></TD></TR>
<TR>
<TD><STRONG>7</STRONG></TD>
<TD><STRONG>10406</STRONG></TD>
<TD><STRONG>20</STRONG></TD></TR>
<TR>
<TD><STRONG>8</STRONG></TD>
<TD><STRONG>10102</STRONG></TD>
<TD><STRONG>13</STRONG></TD></TR>
<TR>
<TD><STRONG>9</STRONG></TD>
<TD><STRONG>10301</STRONG></TD>
<TD><STRONG>4</STRONG></TD></TR></TBODY></TABLE> </P>
<P>my code should do this: what is the lowest "amount in stock" of any appearance of item number in the sheet above? place result in appropriate adjacent cell of each item number in "inventory" sheet (example below)</P>
<P>resulting data should have (without repeated item numbers):<BR>
<TABLE style="WIDTH: 184px; HEIGHT: 243px" cellSpacing=1 cellPadding=1 width=184 bgColor=#ffffff border=1>
<TBODY>
<TR>
<TD></TD>
<TD><STRONG>A</STRONG></TD>
<TD><STRONG>B</STRONG></TD></TR>
<TR>
<TD><STRONG>1</STRONG></TD>
<TD><STRONG>ItemNum</STRONG></TD>
<TD><STRONG>Amt In Stock</STRONG></TD></TR>
<TR>
<TD>2</TD>
<TD>10102</TD>
<TD><FONT color=#ff3333>13</FONT></TD></TR>
<TR>
<TD>3</TD>
<TD>10203</TD>
<TD><FONT color=#ff3333>9</FONT></TD></TR>
<TR>
<TD>4</TD>
<TD>10406</TD>
<TD><FONT color=#ff3333>20</FONT></TD></TR>
<TR>
<TD>5</TD>
<TD>etc...</TD>
<TD>etc...</TD></TR>
<TR>
<TD>6</TD>
<TD></TD>
<TD></TD></TR>
<TR>
<TD>7</TD>
<TD></TD>
<TD></TD></TR>
<TR>
<TD>8</TD>
<TD></TD>
<TD></TD></TR>
<TR>
<TD>9</TD>
<TD></TD>
<TD></TD></TR></TBODY></TABLE></P></BODY></HTML>
 
Upvote 0
really sorry about the space above the tables, im a new user to the html editor and i dont know how to get rid of that....
 
Upvote 0
minwh.jpg


Formula in B13(array) confirm CTRL+SHIFT+ENTER:
=MIN(IF($A$2:$A$9=A13,$B$2:$B$9))
 
Upvote 0
i had to tweak it a bit of course for the correct field names etc but it worked! only with the ctrl+shift+enter, without it completely did not work, that was key. great job

thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
How to reduce inventory from the same SKU
Replies
1
Views
300
bobsan42
bobsan42
R
  • Solved
Show most recent value within column (furthest row down)
2
Replies
16
Views
436
Peter_SSs
Peter_SSs
M
  • Question Question
Macro/Automating Edits and Calculations for Large Report (40K rows)
Replies
6
Views
204
kevin9999
K
P
  • Question Question
Matching an ID from 2 columns in 2 worksheets to then produce the figure from a 3rd column.
Replies
4
Views
452
Pavesib
P
M
  • Solved
Do I have to repeat the same procedure to match combobox with two sheets and different columns
Replies
3
Views
325
dmt32
D

Forum statistics

Threads
1,226,693
Messages
6,192,466
Members
453,726
Latest member
JoeH57

Share this page

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