Taking data from Master Table into sub-tables

**aquaman21** · 08-04-2016, 07:32 AM

Hi everyone,

This is my first time here and I'm a self-admitted newbie at Excel. I think this might be a simple problem but I really, really, really don't know how to proceed. Any help would be great. Honestly. I'm using Microsoft Excel 2010.

Now in my Sheet 1, I have created a Table with data which I refer to as Master database.

It looks like this:

Date Last Name First Name Email Mobile Number
02/1/2016 James Henry [email protected]
03/1/2016 Tim 76543-84344
04/1/2016 Wales A [email protected] 78475-443495

Now basically this is input from consumers and sometimes we either have a entry for E-mail Address or for Mobile Number and sometimes it's blank. Now I am creating two other tables on a different sheet, same workbook.

One is called SMS database. One is called E-mail database.

Now whichever entry in my Master database has a mobile number (meaning it is not left blank in the mobile number column), I want all of that information to be automatically picked up from the Master database onto this SMS database sheet.

So I want it to look something like this:

Date Last Name First Name Email Mobile Number
03/1/2016 Tim 76543-8434
04/1/2016 Wales A [email protected] 78475-443495

And similarly, any entry with text in e-mail address column, I want that whole info including Date, Names etc) to be copied onto the sheet Email database.

So I want it to look something like this:

Date Last Name First Name Email Mobile Number
02/1/2016 James Henry [email protected]

And I want this to keep happening automatically in the other two sheets everytime I fill out a Master dabatase.

Can anyone help me please?! I really need help in this.

Thanks in advance!

**mikeTRON** · 08-04-2016, 06:08 PM

I would use your master like you have then create pivot tables to automatically pull the data to other sheets in the format you want. It is essentially ONE data set with a few reports on other sheets.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**Pete_UK** · 08-04-2016, 06:16 PM

An alternative approach would be to have two helper columns in the master sheet, which identify those records with email addresses and those with mobile numbers. Then in the subsidiary sheets you would just need an INDEX/MATCH formula copied across and down. This would update automatically whenever you added new records to the master sheet.

As Mike says, it would be helpful if you attached a sample Excel workbook.

Hope this helps.

Pete

**CAntosh** · 08-05-2016, 01:05 PM

The solutions above are both good ones. Alternatively, you can also tie your SMS and email lists to the master using an ugly (but effective) formula. If your master sheet is called 'Master', you can put the following array formula in A2 (assuming headers) of your SMS sheet, confirm it with Ctrl + Shift + Enter instead of Enter, fill it right through column E, then fill down as far as you think you'll need:

=IFERROR(IF(INDEX(Master!$A$2:$E$200,SMALL(IF(LEN(Master!$E$2:$E$200)>0,ROW(Master!$E$2:$E$200)),ROW(1:1))-1,COLUMN(A:A))=0,"",INDEX(Master!$A$2:$E$200,SMALL(IF(LEN(Master!$E$2:$E$200)>0,ROW(Master!$E$2:$E$200)),ROW(1:1))-1,COLUMN(A:A))),"")

Repeat the process on your email sheet with the following:

=IFERROR(IF(INDEX(Master!$A$2:$E$200,SMALL(IF(LEN(Master!$D$2:$D$200)>0,ROW(Master!$D$2:$D$200)),ROW(1:1))-1,COLUMN(A:A))=0,"",INDEX(Master!$A$2:$E$200,SMALL(IF(LEN(Master!$D$2:$D$200)>0,ROW(Master!$D$2:$D$200)),ROW(1:1))-1,COLUMN(A:A))),"")

Both of these formulae are set to cover the first 200 rows of your master sheet. If you need a broader range, change all of the '200's in the formula to something bigger. They should automatically update as you add/subtract/change rows on your master sheet. See attached for a sample.