How can create a new sheet by comparing two sheets with an "in"-like query?

Question

I'm facing a problem where I have a two excel sheets from SalesForce CRM which I need to use to filter out articles which are in a certain category. These are originally downloaded from the SF knowledge platform and the correspond to the article-type and category objects (tables) in SalesForce that have been dumped by the data loader. Here are samples from each:

Categories

ID                  PARENTID            GROUPNAME       DATACATEGORYNAME
02oC00000007RoyIAE  ka2C00000004RqwIAE  All_Products    Product1
02oC00000007TAiIAM  ka2C00000004IXuIAM  All_Products    Product1
02oC00000007TB2IAM  ka2C00000004RpFIAU  All_Products    Product2
02oC00000007TPYIA2  ka2C00000004IckIAE  All_Products    Product2

Article

ID                  TITLE
ka2C00000004RqwIAE  How to do this
ka2C00000004RqmIAE  How to do that
ka2C00000004RpFIAU  My product exploded
ka2C00000004RpFXYZ  Some title
ka2C00000004RFbIAM  How does group licensing work?

The problem I'm faced with is that I only want the Articles in Article where Categories.DATACATEGORYNAME is "Product2." In C-Variant psuedocode, I'd do something like:

List<CustomObject> final = new List<CustomObject>(); //Note that customObject would have fields for each of my final desired values
for (row c in categories)
{   
    for (row a in article)
    {
        if (c.PARENTID == a.ID)
        {
            final.add(new CustomObject { ID = a.ID, TITLE = a.TITLE });
        }
    }
}

I'd then take this list and print it to a CSV file or something like that.

Using an alternative technology like SQL, I'd do something like an "in" query. What I'm wondering is: Is there a way to do something similar to this in Excel?

I ended up finding a third party application to import the data into SQL, but would like to know if this could be done with excel. — Darkenor, Nov 12 '13 at 19:24
The simplest would be to use [Autofilter](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) is an example. — Siddharth Rout, Nov 12 '13 at 20:19

Ross Brasseaux · Accepted Answer · 2013-11-12T20:05:37.343

Here is a basic solution. I'll add some explanation comments in a second.

Sub test()


Dim i As Integer, j As Integer, k As Integer 'Define a few integer variables for counting.
Dim bookReport As Workbook, Art As Worksheet, Cat As Worksheet 'Define worksheet and workbook variables.
Dim newSheet As Worksheet 'Define variable for new worksheet (for output).

Set bookReport = Excel.ActiveWorkbook 'Set book variable to active workbook.
Set Art = bookReport.Worksheets("Article") 'Set Art to worksheet named "Article"
Set Cat = bookReport.Worksheets("Categories") 'Set Cat to worksheet named "Categories"
Set newSheet = bookReport.Worksheets.Add 'Create a new worksheet and assign it to the newSheet variable.

k = 1 'Set a starting point for "k". This variable will be used to keep track of our location on the output sheet.
For i = 1 To Cat.UsedRange.Rows.Count 'We'll use the i variable to loop through each row in the used range of the "Categories" sheet...
    For j = 1 To Art.UsedRange.Rows.Count '...and use the j variable to do the same for the "Articles" sheet.
        If Art.Cells(j, 1).Value = Cat.Cells(i, 2).Value Then 'If the first column of the [j] row in the "Articles" sheet equals the second column of the [i] row in the "Categories" sheet, then...
            newSheet.Cells(k, 1).Value = Art.Cells(j, 1).Value 'Insert the value of the first column of the [j] row in the "Articles" column into the first column of the [k] row in our new worksheet.
            newSheet.Cells(k, 2).Value = Art.Cells(j, 2).Value 'Do the same with the second column.
            k = k + 1 'Increment our tracking variable.
        End If 'Exit the conditional.
    Next j 'Increment j and repeat
Next i 'Increment i and repeat



End Sub

How can create a new sheet by comparing two sheets with an "in"-like query?

1 Answers1