Making an Excel List, Please help :)

rm67177n

New member
Joined
Jul 19, 2017
Messages
2
Reaction score
0
Points
0
Hello,

I have a list (list 1) with 100+ names of companies, many of them repeating. I am trying to make a new list (list 2) that has each of the companies in list 1 just listed once in list 2. And in list 2 it will automatically add any new companies that are put into list 1 into list 2.

I have made an example below:


List 1:


Type of Food
Person its from
Amount
Apples
betty
3
Apples
george
4
Oranges
cassie
2
Burger
lucy
1
Fries
matt
3


-List 1 has the breakdown of the Types of foods (company) and the amount of each that the person has.

List 2:


Food
Amount
Apples
7
Oranges
2
Burgers
1
Fries
3

-List 2 is suppose to take the type of food in list 1, display it once and add any new foods that are currently not in list 2.

Issue:
-If I add a new food into list 1 like pasta, what formula do I use in list 2 to make it show up ? I understand how to do the amount column for list 2. I am just really confused on how to do the food column for list 2
 

retired007geek

New member
Joined
Jun 26, 2017
Messages
86
Reaction score
0
Points
0
Location
South Carolina, USA
RM,

Here's a sample workbook that almost solves your problem as it is not totally automatic! It does require you to press a button (located on the Criteria Page) after adding items to the database (list) but the rest is on auto pilot.

The solution takes advantage of:
  1. VBA Code
  2. Named Ranges
  3. Advanced Filters
  4. SumIf Function

The VBA Code:
Code:
Option Explicit

Sub UpdateList()

   Dim lRowCntr As Long
   Dim wksDatabase As Worksheet
   
   Set wksDatabase = Sheets("Database")
   
   '*** Clear Extract Sheet ***
    
    Sheets("Extract").Activate
    Cells.Select
    Selection.ClearContents
    
   '*** Extract unique entries to the Extract Sheet ***
    
    Range("A1").FormulaR1C1 = "=" & wksDatabase.Name & "!R1C1"
    Range("A1:A2").Select
    Range("DataBase").AdvancedFilter Action:=xlFilterCopy, _
                      CriteriaRange:=Range("Criteria"), _
                      CopyToRange:=Range("Extract"), _
                      Unique:=True
             
   '*** Add SumIf formulas to get counts ***

    Range("B1").FormulaR1C1 = "Count"
    lRowCntr = 2
    [B1].Select
    
    Do While (Cells(lRowCntr, 1) <> "")
         Cells(lRowCntr, 2).FormulaR1C1 = "=SUMIF(Database!C1," & _
              Cells(lRowCntr, 1).Address(, , xlR1C1) & ",Database!C3)"
      lRowCntr = lRowCntr + 1
    Loop
        
End Sub  'UpdateList

Sample File: View attachment ExtractExample.xlsm

HTH :yo:
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,156
Reaction score
4
Points
38
Excel Version(s)
365
Consider also using a pivot table - of the most basic kind.
Attached is retired007geek's file adapted:
No code (although a line or two could be added to refresh the pivot table)
One dynamic named range retained (Database)
One pivot table; to update the pivot, right-click in it and choose refresh.

As an aside, although you can use the name 'Database', it's a good idea not to, either as a named range or a sheet name since Excel (perhaps older versions only now) uses this internally for its own purposes.
 

Attachments

  • ExzcelGuru8080ExtractExample.xlsx
    12.1 KB · Views: 14

retired007geek

New member
Joined
Jun 26, 2017
Messages
86
Reaction score
0
Points
0
Location
South Carolina, USA
Consider also using a pivot table - of the most basic kind.
Attached is retired007geek's file adapted:
No code (although a line or two could be added to refresh the pivot table)
One dynamic named range retained (Database)
One pivot table; to update the pivot, right-click in it and choose refresh.

As an aside, although you can use the name 'Database', it's a good idea not to, either as a named range or a sheet name since Excel (perhaps older versions only now) uses this internally for its own purposes.

p45cal,

A superior solution! :cool:
 
Top