Creating a database in excel

Question

I'm working with a database in excel. I will try to make it as simple as possible.

For example,

I have a vlookup range/array of fruits, and who likes each fruit.

    Fruit - Person
1.  Apple – DeShoun
2.  Apple – John
3.  Apple – Scott
4.  Pear – Scott
5.  Strawberries – John… ect

In my database I have a list of fruit and the vendor that sells it

    Fruit - Vendor
1.  Apple – Sprouts
2.  Apple – Walmart
3.  Apple – Trader Joe’s
4.  Strawberries – Abel Farms
5.  Banana – Sprouts
6.  Pear – Sprouts…. ect

I need to be able to find the fruit “apple” within my database and create new rows of information within the database so that it looks like the following.

    Fruit - Vendor - Person
1.  Apple – Sprouts - DeShoun
2.  Apple – Walmart - DeShoun
3.  Apple – Trader Joe’s - DeShoun
4.  Apple – Sprouts - John
5.  Apple – Walmart – John
6.  Apple – Trader Joe’s - John
7.  Apple – Sprouts - Scott
8.  Apple – Walmart - Scott
9.  Apple – Trader Joe’s – Scott
10. Strawberries – Abel Farms - John
11. Banana – Sprouts - #N/A
12. Pear – Sprouts - Scott

Since I will be working on a minimum of 1000+ rows, I need to know if there’s there a process to expedite this in any way.

Does anyone have any suggestions or links/articles that can point me in the right direction?

Feel free to comment or ask any questions that could help lead to a good answer.

Thanks

Answer 1

It can be difficult to get your head round at first, but I would recommend looking into the INDEX MATCH functions. Used together they can do exactly what vlookup does, but with a little understanding they are far more flexible and may well be better suited to your needs :) http://fiveminutelessons.com/learn-microsoft-excel/how-use-index-match-instead-vlookup

Might be helpful, or google to find a tutorial that suits you

Specifically for your problem, the hardest part will be matching every vendor, person to each fruit... VBA might be necessary

Answer 2

I'm pretty new to VBA, but had a bit of a fiddle around and this seems to work sort of as you describe (as a potential example...). Have put each table of data on a separate worksheet.

Sub FruityPerson_Matching()

Dim strFruit As String, strPerson As String, strVendor As String  'to hold text.
Dim myWB As Workbook, myWS_P As Worksheet, myWS_V As Worksheet, myWS_C As Worksheet
Dim LastRow As Integer, n As Integer, iNewRow As Integer
Dim rFruit As Range, checkCell As Range

Set myWB = Application.ActiveWorkbook
Set myWS_P = myWB.Worksheets("Person")
Set myWS_V = myWB.Worksheets("Vendor")
Set myWS_C = myWB.Worksheets("Combined")

LastRow = myWS_P.Cells(myWS_P.Rows.Count, "A").End(xlUp).Row    

First looping through list of people, finds the first instance of their fruit within the vendors list:

For n = 2 To LastRow
        strFruit = Cells(n, 1).Value
        strPerson = Cells(n, 2).Value 

    Set rFruit = myWS_V.Range("A:B").Find(What:=strFruit, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

    If Not rFruit Is Nothing Then
        Set checkCell = rFruit    'For checking when findnext gets back to original cell.
        strVendor = myWS_V.Cells(rFruit.Row, 2).Value

Add this to a new row (so that sure it is blank) in the final combined data sheet:

        iNewRow = myWS_C.Range("A" & myWS_C.Rows.Count).End(xlUp).Offset(1).Row

        myWS_C.Range("A" & iNewRow).Value = strFruit
        myWS_C.Range("B" & iNewRow).Value = strVendor
        myWS_C.Range("C" & iNewRow).Value = strPerson   

Since potential multiple vendors per fruit, now looping through them for same person:

        Do
            Set rFruit = myWS_V.Range("A:B").FindNext(After:=rFruit)

            If Not rFruit Is Nothing Then
                If rFruit.Address = checkCell.Address Then Exit Do  
                         'Shows: are back at start.
                strVendor = myWS_V.Cells(rFruit.Row, 2).Value

                iNewRow = myWS_C.Range("A" & myWS_C.Rows.Count).End(xlUp).Offset(1).Row

                myWS_C.Range("A" & iNewRow).Value = strFruit
                myWS_C.Range("B" & iNewRow).Value = strVendor
                myWS_C.Range("C" & iNewRow).Value = strPerson

            Else
                Exit Do
            End If
        Loop
    Else
        'What do if strFruit not found...?
        Exit Sub
    End If

    Next

End Sub

Finally moving on to next person in loop etc until reaching the last row of data.

Something like what you had in mind?

Answer 3

Let's say your Fruit-Person table is Table 2, Fruit-Vendor is table 3. Fruit is the common field across tables here. You will need to build a Table 1 with unique values from Fruit column. (There are many ways of building a table with unique values, if you aren't aware, they should be available online)

I am listing the process for Excel-2013, there is a chance it might be slightly different in the older versions.

Step 0:

You have 3 tables as per earlier description.

Step 1:

Convert all of them to Tables one by one. Alt+N>>T, or, select A1:A5 >> Insert >> Table. Tick Choose My Table has Headers.

Repeat this process for all 3 tables. They should look like this:

Step 2:

Create Pivot Table on Multiple Ranges A) Create Pivot table on Table 1 (Insert>>PivotTable). Tick check "Add this data to Data Model". IMP

B) Under Pivot Table fields, ALL; you should see all 3 tables

Step3:

Create Relationships In the Analyze tab, click Relationships. A box which says Manage relationships should open up. The idea is to build relationships.

A) Try building a relationship between Table 1 and Table 2. New >> choose following options:

Table: Table 2

Column (Foreign): Fruit

Related Table: Table 1

Related Column (Primary): Fruit

B) Let's try building it now between Table 1 & 3 New >> choose following options:

Table: Table 3

Column (Foreign): Fruit

Related Table: Table 1

Related Column (Primary): Fruit

It should look like this:

Step 4:

Forming the Pivot A) Get Fruit from Table 1, Person from Table 2, Vendor from Table 3 (in that order) as row labels

B) Now, Table2/Fruit and Table3/Fruit need to go as Value Labels.

The table so formed is your almost final table. The rows you want will be the ones which have a 1 in column D and E both. You can get those rows off by filtering/pasting as values.

(As a process, pasting images isn't the popular method it seems, but I couldn't have explained it better visually without them)