PDA

View Full Version : [SOLVED] Summarize Data without using Pivot Table



Jill
07-13-2017, 10:11 PM
Hi All,

I'm fairly new to excel and have been trying to solve this for a few days. I've tried formulas and macros. Essentially I want to take a list of data (two columns). If the data in row 1 (2 columns) matches another row exactly (2 columns) then count and copy the data. Here is an example.


Turn this data


1.63 x 1.63 x 1
Diamond_Round


1.87 x 1.87 x 1.15
Diamond_Round


1.87 x 1.87 x 1.15
Diamond_Round


2.32 x 2.32 x 1.43
Diamond_Round


1.63 x 1.63 x 1
Diamond_Round


1.63 x 1.63 x 1
Diamond_Round


1.87 x 1.87 x 1.15
Diamond_Round


2.32 x 2.32 x 1.43
Diamond_Round


1.87 x 1.87 x 1.15
Diamond_Round


1.63 x 1.63 x 1
Diamond_Round





Into this data


4
1.63 x 1.63 x 1
Diamond_Round


4
1.87 x 1.87 x 1.15
Diamond_Round


2
2.32 x 2.32 x 1.43
Diamond_Round




As background info, I have a script that runs in another program and populates an excel with this bill of material info for each product sku. So the data ported to the excel is never the same. I had luck with a pivot table, but as soon as the data changes, the table no longer works.

I'm attaching a spreadsheet with some sample data.

I've read countless posts and feel I'm at a dead end. Any help is appreciated.

Thanks in advance,
Jill

mancubus
07-14-2017, 12:41 AM
Formula in J5:

=COUNTIFS($C$5:$C$5000,K5,$E$5:$E$5000,M5)

copy down.

Jill
07-14-2017, 07:14 AM
Thanks for your help mancubus!

But the formula relies on K and M. How do I get column K and M to populate? Essentially I need all of the info on the right under "results Im trying to achieve" using only the info on the left.

Is this possible? I's stumped.

Jill
07-14-2017, 08:14 AM
I tried this but it doesn't get by the False cells.

{=IFERROR(INDEX($C$9:$C$18, MATCH(0,COUNTIF($P$5:P5, $C$9:$C$18), 0)),"")}

mdmackillop
07-14-2017, 10:25 AM
Delete the merged cells!

Sub Test()
Columns("C:E").Copy Range("K1")
Range("$K:$M").RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlNo
Set r = Range("K1").End(xlDown)
Range(r, r.End(xlDown)).Offset(, -1).FormulaR1C1 = "=COUNTIFS(C[-7],RC[1],C[-5],RC[3])"
End Sub

Jill
07-14-2017, 01:00 PM
Mdmackillop! That worked.

I'm not entirely sure what it all means....yet. But I will begin researching and dissecting the code so I can learn from this.

I am very grateful for your generosity. You have helped me in the past too. Many many thanks!

Jill

mancubus
07-14-2017, 01:22 PM
i thought you were looking for a formula only.

below is a macro solution. change range references to suit your original file (it seems the workbook you have uploaded is not the sames as your original file)


Sub vbax_60069_count_2cols_based_distinct_valz()

Range("C5:E5000").Copy Destination:=Range("K5")
Range("$K$5:$M$5000").RemoveDuplicates Columns:=Array(1, 3), Header:=xlNo
With Range("J5:J" & Range("K" & Rows.Count).End(xlUp).Row)
.Formula = "=COUNTIFS($C$5:$C$5000,K5,$E$5:$E$5000,M5)"
.Value = .Value
End With

End Sub

mancubus
07-14-2017, 03:25 PM
aha.

posting to the thread without refreshing the page.
:devil2::dunno:banghead:

Jill
07-14-2017, 03:54 PM
That works too! You both are the best. Really appreciate all the help!

Jill