Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the out put from different cells
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the out put from different cells
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 On Jul 17, 11:57*am, EBnLP01 wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the out put from different cells
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" wrote: 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 On Jul 17, 11:57 am, EBnLP01 wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the out put from different cells
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 On Jul 17, 2:44*pm, EBnLP01 wrote: 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" wrote: 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 On Jul 17, 11:57 am, EBnLP01 wrote: 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- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the out put from different cells
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 ! "Ken" wrote: 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 On Jul 17, 2:44 pm, EBnLP01 wrote: 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" wrote: 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 On Jul 17, 11:57 am, EBnLP01 wrote: 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- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
Cells won't change font color or show hi-lighted cells in document | Excel Discussion (Misc queries) | |||
display a range of cells editible cells based on matching date | Excel Worksheet Functions | |||
Setting of input cells as blue font and formula cells as black fon | Excel Discussion (Misc queries) | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions |