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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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

P
  • Question
Matching an ID from 2 columns in 2 worksheets to then produce the figure from a 3rd column.
Replies
4
Views
87
Pavesib
P
M
  • Solved
Do I have to repeat the same procedure to match combobox with two sheets and different columns
Replies
3
Views
166
dmt32
D
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
375
Alex Blakenburg
A
K
  • Question
Replacing Lowest value
2
Replies
14
Views
429
KrisGaczewski
K
K
  • Question
Populate column C with repeated entries based on count for each row in column A?
Replies
3
Views
189
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,216,748
Messages
6,132,492
Members
449,730
Latest member
SeanHT

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