Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Consolidating by more than 1 column

0

Hey folks, I can usually either figure something out or find an example online that works or almost works and incorporate it into what I need to do. Not this time though. Can anyone help me out here?

I have four columns of data. In column A, there is a size designation that could be one of any different 12 sizes.

Column B contains a length that could be any given length and is displayed with a (4) place decimal.

Column B could also contain duplicates of any or all lengths in any or all of the 12 sizes.

Column C is column B rounded to the nearest half inch.

Column D is populated with the number 1 used as a counter .

In columns E and F I use the Consolidate Command to consolidate the data by using columns C and D placing the length in column E and the consolidated quantity in column F.

This works great until I wish to include the data in column A. It is possible to have lengths that are the same except for the initial size. I need these to be totaled up by size rather than length.

The information in Columns A and B is sent to Excel from a AutoLisp program in AutoCAD and could be as little as (2) rows or as many as 100 plus.

I have included an example. (example.xls)(Note: The example contains (9) entries. Reality is infinite)

The Rounded Length column is the formula:  =mround(B3,0.50)

Counter Column is the formula:  =IF(B3  >1,1," ")

excelconsolidate-data
MS Office Version: 2010
posted by: Mreuter (rep: 2) on Mon Sep 11, 2017 at 2:08 pm
Answer
Discuss

Answers

0
Selected Answer

On your spreadsheet I used the following method to recreate the desired result you have in columns F:H in columns J:L.

  1. J3 = $A3
  2. K3 = $C3
  3. L3 = COUNTIFS($A$3:$A$100,$A3,$C$3:$C$100,$C3)
    Replace 100 with a generously number greater than the largest number you ever expect.
  4. Copy the formulas down to the last row (row 11 in your example).
  5. Select the range in which you have the formulas (J3:L11)
  6. Copy and Paste > Values (to replace the formulas with hard numbers)
  7. From the Ribbon's Data tab select "Remove duplicates"
  8. In the dialog that opens, select columns J and K
    (or select all and deselect column L)
  9. Press OK. Two duplicate items will be removed.
  10. Select the remaining range.
  11. Still on the Data tab, click Sort.
  12. Set: Sort by Column J, Sort on Values, Order Z to A
  13. Click OK. Specify: "Sort anything that looks likke a number, as a number."
  14. OK.

In practise, I would recommend that you do steps 5 and up on a copy of the data.

posted by: Variatus (rep: 4889) on Mon Sep 11, 2017 at 9:39 pm
Discuss

Discussion

I had forgot about the COUNTIFS... Works fantastic....Thank You!
Mreuter (rep: 2) Sep 13, '17 at 9:21 am
Add to Discussion
0

you could definately put the info in a pivot table.

posted by: queue (rep: 467) on Mon Sep 11, 2017 at 2:18 pm
Discuss

Discussion

Sorry, I forgot to mention (edited the post already) that the information is sent to a new Excel file from AutoCAD everytime it is used and is placed in Columns A and B. I run a macro at startup to add the formulas to columns C and D.
Mreuter (rep: 2) Sep 11, '17 at 2:27 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login