Expert Excel HELPPPP

[BadHavok]

It's hard to explain what I need to do but, I need to add information from one spreadsheet on to another where certain information matches...

here is my example

Spreadsheet Y - 'this is A, and this is B'
and
Spreadsheet Z - 'this is B, and this is C'

Place 'C' in spreadsheet 'Y' - where 'B' in Spreadsheet Z, matches 'B' in spreadsheet Y

before you say Vlookup, it's not correct, there are multiple instances of 'B' on Spreadsheet 'Y'

that's the simple way, and how I need to use it is...

Spreadsheet 1 - 'this is the Account, and this is Address'
and
Spreadsheet 2 - 'this is the Account, and this is the Email address'

Place the Email address in Spreadsheet '1' - where the Account in Spreadsheet 1, matches the Account in spreadsheet 2


I hope that makes sence, and that there is someone out there that can help, as I have to do many of the above, equaling to around 23,000 lines in Excel!! (a few days work to those who do it by hand)

 

[Dolph]

Do you have access to a database system (such as MS access)? If so link the tables and create a query to give you A B and C in one spreadsheet (along with any other information needed from either spreadsheet) and export it to excel.

This does rely on each row have some sort of common unique identifier (such as the account number)

If you don't have access, you can do the same with Index and Match, but that can be flakey across seperate spreadsheets.

 

[BadHavok]

Thanks for your reply!

I do have Access but I'm not familiar with that at all.

Index and Match is exactly what I am trying to use, however, I've no idea how to actually write the formula...

The Problem I have is there are many copies of the same account number with various other details that I need the same email address linked to (which is why I can't do a simple copy/paste)

 

[Dolph]

The normal way would be :

=index(data range, match(account no, data range, false), column number of data to be returned)

This assumes the data is 1 row per entry, if it's one column per entry move the match to the second part and put the fixed row number of the data to be returned in.

As for the same account numbers being present multiple times, it shouldn't be a problem provided they all need the same email address.

 

[BadHavok]

Okay, that's where I get confused

So, for example...

Spreadsheet 1 Spreadsheet 2
A B C A B C
Acc No Name 'Blank' Acc No Email 'Blank'


I can't get my head around where everything is placed.

It will use whole columns, and I do need the same email address with the Acc No

 

[suarve]

would so much simple to whack the data into access and run a simple query - it really isn't hard at all.

There are articles about using excel as a database, but they almost always involve very long formulas, using a plethora of functions to do very simple things. I'm sure if you posted the spreadsheet with sample data somneone would be able to help you a lot quicker.

 

[Dolph]

ok, in C on spreadsheet 1.

=index(spreadsheet 2!$A$n:$C$n, match($A1, Spreadsheet 2!$A$n:$A$n, false), 2)

Should give the email in column C on spreadsheet 1 (replace the n's with the relevant limits or expected limits of your range).

Or create a new access database, link the two spreadsheets as tables, then click query, simple query wizard and select 'acc no' and 'name' from spreadsheet 1 and 'email' from spreadsheet 2 and hit go