Pull Contact Info from multiple rows and deliver across multiple columns based on company

**ozgothic6568** · 05-08-2020, 11:12 PM

Hi, sorry if the title is confusing, I was trying to be as descriptive as possible

I am trying to combine information from a couple of worksheets into one.

I have a worksheet (#1) with a column for Company Name (A) as well as columns for First Name 1 (B), Last Name 1 (C), Email 1 (D), First Name 2 (E), Last Name 2 (F), Email 2 (G), etc

In another worksheet (#2) I have a list of contacts, ordered by Company Name (A). Each row contains a different contact. So if there are 5 contacts at one particular company then there are 5 rows, each with the same Company Name in column (A) but with differing contact info (First Name (B), Last Name (C), Email (D))

What I would like to do if populate the contact info fields in worksheet #1 with the contact information in worksheet #2, using the Company Name as the identifier

However I don't know how I can assign a different contact in worksheet #2 to a different column in worksheet #1 - or if it's even possible.

I've attached an example workbook that hopefully makes things clearer.

If anyone knows of a way to accomplish this with a formula (or at least not having to type in each contact manually) I'd be very grateful

**Eastw00d** · 05-09-2020, 01:47 AM

Hi, you could use the aggregate function:

Please Login or Register  to view this content.

Cheers
Erwin

**ozgothic6568** · 05-10-2020, 01:20 AM

Thanks so much for the reply and sending back the example spreadsheet, that's great. Unfortunately I can't follow all of the formula and I can't get it working in the actual worksheet I'm using. I'm not sure if that is because there are multiple company names in my worksheet and only 1 in the example I sent???

I understand most of it, although I don't understand what the -ROW('#2'!$D$2)+1 part does, and I'm wondering if that would need to be different in any way when my worksheet has a variety of different company names in the D column?

I've attached an example with a few different company names, just in case you're able to have another look

**ozgothic6568** · 05-10-2020, 01:43 AM

I've just noticed something odd in the example worksheet you sent back. It all looks to be working fine, however if I click on a cell so that the formula appears in the function window and then click anywhere in the fuction window (without actually changing anything in the formula), the cell changes to #NAME?

Would this have anything to do with me using Excel 2007? I read somewhere that AGGREGATE was an addition to Excel 2010, so I'm wondering if that might be why it's not working perhaps?

**AliGW** · 05-10-2020, 01:45 AM

The IFERROR and AGGREGATE functions are not available in Excel 2007 - they were introduced in 2010.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

**Eastw00d** · 05-10-2020, 02:06 AM

I have just recently learned the power of the aggregate function, in combination with other functions it is a super function, from this youtube channel:
https://www.youtube.com/watch?v=KPhvLMzXa_8
Here you can find the explanation of your "ROW"question.

Cheers
Erwin

**Eastw00d** · 05-10-2020, 02:17 AM

@ Ali and @ ozgothic6568
I am sorry, I completely overlooked that part of version 2007, my bad

Cheers
Erwin

**Eastw00d** · 05-10-2020, 06:40 AM

I have adjusted the formulae to the version of Excel 2007:

Please Login or Register  to view this content.

The next series are the same, with the difference in the COUNTIF-part (green), Note the difference in the range of the F-column

Cheers
Erwin

**AliGW** · 05-10-2020, 07:41 AM

Could you post the formulae in the body of the post as well, please? Thanks.

**Eastw00d** · 05-10-2020, 09:04 AM

I changed post #8

**AliGW** · 05-10-2020, 09:06 AM

Thanks, Erwin.

Pull Contact Info from multiple rows and deliver across multiple columns based on company

LinkBack

Thread Tools

Rate This Thread

Display

Pull Contact Info from multiple rows and deliver across multiple columns based on company

Re: Pull Contact Info from multiple rows and deliver across multiple columns based on comp

Re: Pull Contact Info from multiple rows and deliver across multiple columns based on comp

Re: Pull Contact Info from multiple rows and deliver across multiple columns based on comp

Re: Pull Contact Info from multiple rows and deliver across multiple columns based on comp

Re: Pull Contact Info from multiple rows and deliver across multiple columns based on comp

Re: Pull Contact Info from multiple rows and deliver across multiple columns based on comp

Re: Pull Contact Info from multiple rows and deliver across multiple columns based on comp

Re: Pull Contact Info from multiple rows and deliver across multiple columns based on comp

Re: Pull Contact Info from multiple rows and deliver across multiple columns based on comp

Re: Pull Contact Info from multiple rows and deliver across multiple columns based on comp

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Need to pull multiple rows/columns based off same value in one column

[SOLVED] Pull a sub list of info from a column based on multiple critiria in 2 other columns

[SOLVED] Multiple columns info transposed in multiple rows

Matching Contact Info to Company Info Using ID Number

multiple cells split to multiple rows with other columns copying their info down

Vlookup/Match: pull info from multiple columns

[SOLVED] Address and contact info from columns to rows

Bookmarks

Bookmarks

Posting Permissions