Getting the out put from different cells

[EBnLP01]

You guys have helped me out alot, now here's another one for yall !!

a b c d
1 j1 50 12 30
2 j2 42 15 0
3 j3 46 14 0
4 j4 50 17 0


the output that im looking for would be this :

j1 quantity 2 12,17,30
j2 quantity 1 15
j3 quantity 1 14

basically, i want to :
1) find all the same numbers in column in column b
2) add the quantity of exact matches together
3) and then display the assoisacted numbers from columns c and d (no math to
these numbers...they must stay the same...see above)

Can this be done ?

Thanks for everything !!

EBnLP01

 

[Ken]

I'm a little confused about the data in column A. There are two 50's
in column B, but one is J1 and one is j4. In the output, they both
appear in a j1 row, and there is no reference at all to the fact that
the numbers on the j1 row arose from data in columns c and d that had
50 in column b.

Ken

 

[EBnLP01]

the data in column a is going to be a part number.
the data in column B is going to be the length of the part.
the data in columns c & d are locations of holes to be drilled into these
parts.

i have already made a fix to look at the lengths of the parts, see which
ones match, and then make both of the parts become the same name. the
problem that im having, is getting the hole locations from both parts
combined to display as one.

does that help ?

 

[Ken]

Maybe this will help.

You need a class module and a collection of the unique part numbers.
You go through the data and count each part and build a text string of
the hole locations.

Then you print the collection to the spreadsheet whereever you want
it.

In the code below, I gave your data range (a1:d4) the name "data" and
set it up to print the summary data two rows below the data.

Put this in a class module named UniqueB

Option Explicit

Public B As Integer
Public qty As Integer
Public list As String


Put this in a regular code module

Option Explicit

Dim UniqueBs As New Collection

Sub test()

Dim R As Range
Dim j As Integer
Dim n As Integer
Dim s As String

Dim i As UniqueB

Set R = Range("data")

n = R.Rows.count

'make a collection of unique values from column B

For j = 1 To R.Rows.count
Set i = New UniqueB
i.B = R.Cells(j, 2).Value
i.list = "'"
On Error Resume Next
Call UniqueBs.Add(i, CStr(Format(i.B, "00000")))
Next j

'populate collection with qty and data from columns c and d

For j = 1 To R.Rows.count
s = CStr(Format(R.Cells(j, 2).Value, "00000"))
UniqueBs(s).qty = UniqueBs(s).qty + 1
If R.Cells(j, 3) > 0 Then UniqueBs(s).list = UniqueBs(s).list &
R.Cells(j, 3).Value
If R.Cells(j, 4) > 0 Then UniqueBs(s).list = UniqueBs(s).list &
"," & R.Cells(j, 4).Value & ","
Next j

'output collection to two rows below data

For j = 1 To UniqueBs.count
Cells(n + 1 + j, 1) = UniqueBs(j).B
Cells(n + 1 + j, 2) = "quantity " & UniqueBs(j).qty
Cells(n + 1 + j, 3) = UniqueBs(j).list
Next j

Set UniqueBs = Nothing

End Sub


Good luck

Ken
Norfolk, Va

 

[EBnLP01]

Ken,
Sorry that i have taken so long to reply. I have been working on this and i
just can't figure it out.
I'm still having a problem. I have got it to display everything like you
have, but it's not giving me the correct quantity total. I think the problem
is in the list. I don't see where it is creating a list of parts the
generate a total quantity.

Thanks again for what all you have done, but i still need your help !