Microsoft Excel - Loop

I do not understand loops to well, and I am not sure if it is the way to go.  So, in column C1, I have part numbers and in column D1 are the quantities.  Example: C1 has a part number X11 and D1 has 13 for the quantity.  So when I input a part number in A1, I want the total quantity for that part number to be reflected in B1.  Basically, I want to look up a part number and know how many I have on hand.

Note: The same part number can be listed several different times in column C1 with differnet quantities.  So each row needs to be checked for the part number and the quantities added up.

Thanks,
Dan

Hi Dan
Type this formula in B1 and type your part number in A1. Adjust the range in the formula for your needs
=SUMPRODUCT(($C$1:$C$2000=A1)*($D$1:$D$2000))
HTH
John

Is it possible to create a macro?  I would like to have sheet1 have only each part number in A1 from column C:C listed once and the quantitiy for each part number show in B1.  all the raw data will be on sheet2.  This way I can import the data weekly into sheet2.  Sorry, but after thinking about it, it would be awesome to just run a macro that would look at the raw data on sheet2, and display each part number once and show the total quantity for each part number on sheet1.  I need help, because I am still learning Excel, and this is beyond my experience at this time.

I really appreciate you help.

Dan

Dan
You can use the formula in sheet1 and it will look up in sheet2, like this :
=SUMPRODUCT((Sheet2!$C$1:$C$20=A1)*(Sheet2!$D$1:$D$20))
You just type the part number in A1 or change that to your liking, change A1 in the formula.
John

I am trying to figure out how to run a macro to get all the part numbers because I can not remember all the part numbers, so I have to constantly good to sheet2 and look through at all the part numbers to make sure I did not miss any.

Dan
Now I understand (I hope)!!!
In sheet1 you can create a Data Validation list ( It's a list box that you can see all your parts, select and the qTy will show up with the formula in the other cell).
First, on sheet 2 select all your part number, should be all in one column.
then go to menu> Insert >Name>Define and give your list a name. Just for example will call it parts
Back to sheet1, select the cell you want (A1) should be the same as in the formula.
Go to menu >Data>Validation and in the firat box select List and the second box type  =parts
Now you should have a drop down list showing you all your parts and if you select one the quantity will show up with the formula.
HTH
John

That is better, but when I select the drop down it may show 50 of part number 123 and 130 of part number 456. So I have to go down the list and select each part number to see whatt the total is.  This will be time consuming.  Is there a macro that can look at column C:C on sheet2 and select each part number once and then paste each part number once in column A:A on sheet1?  This way I can see all the part numbers once and what the totals are.  This will save me alot of time.

Dan

Hi Dan,

Your question here do not need to use any macro, just simple formula is enough.
In cell B2 of sheet 1 (FindStock) input this formula =SUMIF(Data!C:C,FindStock!A2,Data!D:D)

I attach you the file for easy understand...--->Fml_SimpleSumIf.zip

any more request please feel free to ask....

Pichart Y.

Something is wrong.
This formula will give you the total for the same part number, it dos't matter if you have 50 of the same number.
=SUMPRODUCT((Sheet2!$C$1:$C$20=A1)*(Sheet2!$D$1:$D$20))
A macro is not that simple, you would need a Userform and much more work.
John

Nope, niether works.  Here is the problem.  If I use a drop down lisyt it lists all duplicate part numbers.  This drop down can become huge and I waste time scrolling through it.  Imagin one part number being listed in the raw date 100 times, but with different quantities.  I would have to scroll through the drop down until I get to the next part number.

This is why I need a macro to just pull each part number once.  I could have 20 part number that are duplicated 100 times each.  A drop down would go on for ever.

Dan
Place this macro in a module.
Option Explicit
Sub UniqueNames()
    Dim ws As Worksheet
    Dim rg As Range, c As Range
    Dim rDest As Range
    Dim col As New Collection
    Dim i As Long
    
Set rDest = Worksheets("Sheet2").Range("A1")
Set col = New Collection


For Each ws In Worksheets
    If ws.Name = "Sheet2" Then Exit For
 With ws
    Set rg = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
    For Each c In rg
        On Error Resume Next
            col.Add c.Text, c.Text
        On Error GoTo 0
    Next c
  End With
Next ws
For i = 2 To col.Count
    rDest.Offset(i - 1, 0).Value = col(i)
Next i
End Sub
And on sheet2 in cell B2 place this : =SUMPRODUCT((Sheet1!$A$2:$A$200=A2)*(Sheet1!$B$2:$B$200)) and copy down
The macro will copy on sheet2 all the unique part# and the formula will give you the total amount for each item.
HTH
John

Dan
 Don't forget to adjust the range in the formula, I have it to 200 only.