Excel help needed ?

[UBT - Timbo]

Hi all,

I need a bit of help on Excel spreadsheets.

I have 2 spreadsheets: The first has a table of data, incorporating three columns of info: call them A, B and C (in about 5000 rows).

The second sheet also has a table of data with columns of info that are similar in that the first two columns share the same data as the 1st sheet, namely A and B, but the 3rd column has info D. This sheet has about 500 rows.

I want to build a "result table" (maybe in a new worksheet, linked to the first 2), where if there is a exact match between data in column A of sheet 1 matches data in column A of sheet 2, then info from column C from the first worksheet (that corresponds to more than a certain value), and info from column D of the 2nd sheet "produces" a result table that gives me columns of info: A, C and D.

I've seen this done in Excel before, but for the life of me I can't remember how - I've looked up VLOOKUP, MATCH and INDEX but they just give single results from a single worksheet.

The alternative is to just import the data from both sheets into a new sheet, do a sort on the 1st three columns of data, do another sort on the 2nd three columns of data and then do a manual cross-check.

But in the back of my mind I'm sure there's a more elegant way.

regards
Tim

[UBT - Mikee]

Hi

It must be over 20 years since I used formulas in excel but (I know this is wrong but it may give a pointer) use the fx function and drop down menus to bring up the logical catagory then use if, or and other functions to write the formula you want. Something along the lines of

iF (sheet1!c1) and (sheet1!d1) = (sheet3!d1) then copy (sheet2!c1) to (sheet3!d4), probably need to use the < and > in there as well.

once you have the formula right copy it and adjust for the next line, then you can highlight both lines, click on the little square at the end of the last line and drag down as many lines as you like to automatically paste and modify.

Mike

[UBT - Timbo]

Hi Mike

Thanks for that

I'd not thought of using the IF function.

I'm going to have to specify quite a parameters on this as two of the columns (one on each "donor" sheet) holds similar data, although not all the rows are the same.

What I'm trying to do is to build an email database of active UBT members...as there is no other way of doing this - projects don't allow "founders" to email their team members directly - and even if they did, one would have to do it on multiple projects at the same time... so, boo hiss to the BOINC devs for not allowing this.

Here's what I've got::

Sheet 1 is a cut and paste from ubtstats.co.uk and this shows username and whether they are active - the fact ubtstats doesn't have every project isn't much of a problem, as most members are crunching on most of the popular projects which are included - other data is also in the other columns, but it's not relevant for this purpose.

Sheet 2 is gleaned from a number of different project websites (where I'm admin) - so that has columns for project name (input by me), username and email addy.

So, what I want to do is to compare the two lists and output a third sheet that is basically giving me a list of active users email addresses.

It's essentially merging the two together.

I could do it in Access, by linking two different tables and then linking them together and export the query as a CSV file.

But if it can be done in Excel, then I can re-run this on an ad-hoc basis, every once in a while, just by adding or changing each of the different "project" source files.

So, that's the idea.

regards
Tim

[Woodles]

Hi Tim,

Have you thought of doing it in an Excel macro?

Easy enough to have two pointers stepping through the two column 'A's looking for a match and a writing the correct data to a new entry on a third spreadsheet?

You can then run it as and when you change any of the data.

If you want to send me a spreadsheet with dummy information in of the right format I'll knock one up for you.

Regards,

Mark

[UBT - Timbo]

HI Mark

Thanks for the offer...I'm at work now, so I'll perhaps create a "dummy" for you later when I get home.

I've never needed "macros" before so, that's a bit of a "black art" as far as I'm concerned.

In simple terms though, I just have to compare two columns in one spreadsheet (spreadsheet is called "test1.xls") which are "username" and "active" (where the number in the row is >="0") and the second sheet (called "test2.xls") has two columns of text, listing "username" and "email address".

Keeping it simple though, one could assume that for this instance, all the rows where the "active" column is any number is OK, as I can easily delete those rows that are not required.

Of course, I could easily import both sheets into a single (3rd) sheet, so that columns A and B would be from test1.xls and columns D and E would be from test2.xls. Would that be better or help?

regards
Tim

[Woodles]

Hi Tim,

I use them almost daily

I doesn't matter where the data is, either two separate sheets or combined into one, it would only mean changing the identifier in the equation.

I'm also at work at the moment and busy tonight building a new PC so I'll try and knock something up this afternoon with dummy data I'll generate.

Regards,

Mark

[chriscambridge]

DONE!!!!!

(Instructions to follow)..

To create a worksheet containing the Usernames and Email addresses of only those members that are active (via the 2 worksheets), do the following:


1. Turn each of the set of cells (eg Username + Email, and Username + Active) into table's.

Select the cells in the worksheet, including the columns, and click from the menu:

Insert / Table - check the option which says "My table has headers".

** Make sure you have NO empty rows between the columns and data rows **


2. Once you have made each of the table's in the worksheets, create a new blank worksheet..


3. Click from the Menu:

Data / From other sources / From Microsoft Query

(if the resultant window disappears just minimize all windows and you will find it on the desktop)


4. In the Chose Data source window, Click

Excel Files *, and OK.


5. In the Select Workbook window, Click the worksheet that has the "filter" column eg Active, click OK.


6. In the Query Wizard - Chose Columns window, double click the columns to move them to the right, in the Available Tables and Columns box, Click NEXT.


7. In the Query Wizard - Filter Data window, click "Active" in the Column to Filter box, and then chose the first drop down box on the right (labeled Active) and Select "equals". In the right had side of these two boxes, type the value/attribute of the 'active' field.

eg "Yes", or, "1" - or whatever is used to indicate that a member is active, from one that is not. If that makes sense?

Click Next.


8. In the Query Wizard - Sort Order window, Chose the column you want to sort/order the resultant data-set on eg Username.


9. In the Query Wizard - Finish window, click "Return data to Microsoft Excel", and click Finish.


10. In the Import Data window, click OK.


You now have a worksheet containing the Usernames and Email addresses of only those members that are active!

[Woodles]

I don't use SQL so I'm not too sure what you're trying to gain by 'joining' two Excel worksheets? You can always cut and paste data from one worksheet into another unless you want live data from the two worksheets?

In that case:

If they're in the same workbook then they can addressed directly - =Sheet1!CZ8+Sheet2!G13

If they're in different workbooks, make sure they're both loaded then do the same as having two common sheets but also identify the workbook - ='[Workbook1.xlsx]Sheet1'!L199+[Workbook2.xlsx]Progress!IZ11

Ah, it's clearer now you've edited your post

It was best solved with a very simple Macro that stepped through the data on one worksheet, matched it up with the data on the second worksheet and outputted the result onto a third worksheet.

Edited again!

Are you just trying to get two sets of data onto one worksheet? They don't need to be inserted as a table, just cut and paste as you would anything else. Click on the top left cell that you want to copy, drag the mouse to the bottom right cell CTRL-C. Go to the new worksheet, click on your insertion point, CTRL-V

Regards,

Mark

[chriscambridge]

When it comes to data, you should use industry standards, which is SQL.

No, what it is doing, is joing the two tables, on two fields, both Username and Active, but only returning the data where Active is "true/YES/1"

Basically, what above does (via a Wizard) is runs a SQL Select Join statement on Username and Active fields.

Chris

[chriscambridge]

Which is basically the SQL 92 Statement:

SELECT (username, email) FROM (sheet1, sheet2) WHERE (sheet1.username = sheet2.username) AND (sheet1.active = YES);

---

The data I used is for the 2 tables is:

Table1 (eg Excel "table")

Username:

chris
jack
fred

Email:

chris@mail
jack@mail
fred@mail

Table2

Username:

chris
jack
fred

Active:

yes
no
yes

Result

Username:

chris
fred

Email:

chris@mail
fred@mail

[chriscambridge]

Perhaps Tim can confirm this is what is required?

I knew the Year 4 Module in Relational Databases from my Computer Science Degree would come in handy one day..

Chris

[Woodles]

Hi Chris,

Yes, that is what Tim required.

He has two lists in an Excel spreadsheet, one with a column of usernames and another column showing activity (although I believe it's more 'credits earned in the last x days' rather than a simple yes/no. The other list has one column of usernames and another column of email addresses.

What he wanted to do was produce a resulting list of email addresses for each username that is active.

I think your approach would also have worked but since I don't no/use SQL, i did it in Excel.

Regards,

Mark

[chriscambridge]

I think your approach would also have worked but since I don't no/use SQL, i did it in Excel.

It definitly works.. I also used Excel! The steps above are for the Excel query wizard tool.

It looks involved but actually it only takes about 15 seconds to do all the steps.

All you would need to do to get it to work with credits is instead of Active = Yes, is to just use Active > 0.

This is the point I made before I changed the post; I am fairly certain that Excel uses SQL to store and manipulate data, even if you use formulas or other Excel related stuff.

Chris