picking data from multiple sheets

[EARTHWALKER]

A small problem which I hope someone can help with.

I have various sheets, all layed out the same.
Column A = part numbers
Column B = quantity

atm I have 3 sheets. with both of those columns filled in, up to 15,000 records...or so. Various part numbers.

What I want to do is have a new sheet colate the info, and give me the quantity of each part number.

The 3 sheets are from different orders, but each sheet can contain the same info.

So rather than go through the whole sheets counting the quantities of each part number, id like it to tell me.
I thought about a pivot table, but can't get that to work. Tried an hlookup, and was totally baffled.

so if sheet one had pn 111 at a quantity of 12. sheet 2 at 14 and sheet 3 at 4000.
sheet 4 would say, part number 111 - quantity 4026
and carry on down the column giving part numbers with the totals from all the sheets, leaving me a nice end sheet with everything I need.

Is this possible and have I explained it well enough ;D

[NBVC]

If you download and install the free Morefunc.dll addin from this site: http://xcell05.free.fr/english/

You can implement the THREED() function which allows manipulation of multiple sheets.

You must have a list of part numbers in your summary sheet first.... so if these part numbers are in column A of your summary sheet and in your other sheets and column B contains the quantities... then try something like:

=SUM((THREED(Sheet1:Sheet3!A1:A100)=A1),THREED(Sheet1:Sheet3!B1:B100))

Where Sheet1:Sheet3 is the range of sheet names to consider and A1:B100 contain the part information in each sheet.

the A1 represents the first part number in your summary sheet to count.

Note: This is an array formula and once entered/adjusted must be confirmed with CTRL+SHIFT+ENTER keys not just ENTER...you will see { } brackets appear around the formula.... then you can copy it down the list.

[EARTHWALKER]

that's excellent m8, many thanks. I'll give that a try and see what happens.

[MickG]

Hi ,You could try this.
Create a new sheet in your workbook called "Combine"
Run this code from there.
Result from all worksheets (Except "Combine") shown in New sheet "Combine" Columns "A" & "B"

Dim wksh1 As Worksheet, cl As Range, cl2 As Range, rng1 As Range, rng2 As Range
Dim wksh2 As Worksheet, oSet As Double, dic As Object
Set dic = CreateObject("Scripting.Dictionary")
With dic
For Each wksh1 In ActiveWorkbook.Worksheets
    If wksh1.Name <> "Combine" Then
       Set rng1 = Sheets(wksh1.Name).Range("a1", Sheets(wksh1.Name) _
         .Range("A" & Rows.Count).End(xlUp))
     For Each cl In rng1
        
         For Each wksh2 In ActiveWorkbook.Worksheets
             If wksh2.Name <> "Combine" Then
            Set rng2 = Sheets(wksh2.Name).Range("a1", Sheets(wksh2.Name) _
                    .Range("A" & Rows.Count).End(xlUp))
                  For Each cl2 In rng2
                      If cl2.Value = cl.Value Then
                         oSet = oSet + cl2.Offset(, 1).Value
                      End If
                  Next cl2
              End If
          Next wksh2
                If Not .exists(cl.Value) Then
                      .Add cl.Value, oSet
                    End If
                       oSet = 0
       Next cl
    End If
 Next wksh1
End With
 
 Dim Nu, a As Integer
For Each Nu In dic.keys
   a = a + 1
     Cells(a, "A").Value = Nu
        Cells(a, "B").Value = dic.Item(Nu)
      Next Nu
  Set dic = Nothing

Regards Mick