Data Consolidation

Hello,

I am working with some spreadsheets that contain redundant information when I get them and I would like to find a way to make my life easier by finding a way to consolidate the data.

I have a UPC column - this is the column that contains redundancies. Looking at the attached example, I would like to combine the UPC and amount columns (without changing their values) and sum the quantities and totals columns.

I am not sure if there is a way to do this using the consolidate tool.

Thanks!

In J7 and copy right and down,

=SUMIF($B$7:$B$32, $H7, D$7:D$32)

In I7 and copy down,

=K7 / J7

shg,



These two formulas work great for the quantity and total columns. However, the SUMIF formula uses criteria that wouldn't exist on my spreadsheet unless I went through and manually found every different UPC - of which there could be upwards of 100. The UPC and amount columns would need to be generated from the before section before I could use the SUMIF formula you provided. So basically everything that is under the "After" column would be generated from the "before" data.

One part of this sheet that takes the most time is manually putting together a new UPC and amount column. Could it be possible to have a macro go through the UPC column and when ever it finds a new UPC copy and paste it and its amount to a new column?

Thanks!

For a UI approach, you could use Advanced Filter to create a unique list of UPCs. Our you could use a couple of helper columns:

       -A- --B--- -C--
   1   UPC Unique List
   2    1    1     1  
   3   12    12    3  
   4    3    3     4  
   5    9    9     6  
   6    3          7  
   7    2    2     8  
   8   11    11    13 
   9    5    5     15 
  10    1          17 
  11    3          19 
  12   12          20 
  13   15    15       
  14    8    8        
  15   17    17       
  16   19    19       
  17    4    4        
  18   11             
  19   19             
  20    3             
  21   20    20       
  22   16    16       
  23    4             
  24    3             
  25    7    7        
  26    5             
  27    8             
  28    3             
  29    4             
  30   14    14       
  31    3             
  32   13    13       
  33    1             
  34   17             
  35    6    6        
  36    9             
  37   14             
  38   14

The formula in B2 and down is =IF(COUNTIF(A$2:A2, A2)=1, A2, "")

And C2 and down is =IFERROR(SMALL(B2:B38, ROW() - 1), "")

For a VBA approach, you could use a Dictionary object, for which you can find many examples here in the forum.