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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
203
bobsan42
bobsan42
P
  • Question Question
Matching an ID from 2 columns in 2 worksheets to then produce the figure from a 3rd column.
Replies
4
Views
310
Pavesib
P
M
  • Solved
Do I have to repeat the same procedure to match combobox with two sheets and different columns
Replies
3
Views
278
dmt32
D
K
  • Question Question
VLOOKUP on one set of criteria and return multiple matches in a row?
Replies
7
Views
384
Peter_SSs
Peter_SSs
W
  • Question Question
[Excel 365] Maximizing unique combinations of words - No repeats between any two words
Replies
4
Views
323
waffle
W

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

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