+ Reply to Thread
Results 1 to 6 of 6

Multiple match for single company

  1. #1
    Forum Contributor
    Join Date
    02-04-2015
    Location
    India
    MS-Off Ver
    365 (2202)
    Posts
    226

    Multiple match for single company

    Hello,

    I've applied a Pivot from my raw data
    Column A, B, and C has all the same data (see attachment)
    But for a single Company, I have 4 different e-mail ID's (see column D in attached)
    I want all the 4 E-mail ID's in each different cells (see below)

    A B C D E F G
    ABC CORPORATION 121234345 North East Side [email protected] [email protected] [email protected] [email protected]
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Multiple match for single company

    Hi,

    Why not just drag the address field into the Row labels area.
    Admittedly the addresses won't be in a horizontal range of cells, rather a vertical range. Is that not as good?
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    02-04-2015
    Location
    India
    MS-Off Ver
    365 (2202)
    Posts
    226

    Re: Multiple match for single company

    Hi Richard,

    It does not seems to be working. As you are aware, when we do a vlookup, by default it only picks the 1st e-mail id (1st cell value).

    Thanks

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Multiple match for single company

    I don't understand. What has a Vlookup got to do with anything.

    You wanted to use your Pivot Table to show 4 different email addresses in 4 different cells. Functions, Vlookup or otherwise are not involved.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Multiple match for single company

    In Sheet3

    in A2

    =INDEX(Sheet1!$A$2:$A$1000,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A$2:$A$1000),0,0),0))

    in B2

    =VLOOKUP($A2,Sheet1!$A$2:$C$1000,2,0)

    in C2

    =VLOOKUP($A2,Sheet1!$A$2:$C$1000,3,0)

    in D2 and copy across

    =IFERROR(INDEX(Sheet1!$D$2:$D$1000,SMALL(IF(Sheet1!$A$2:$A$1000=Sheet3!$A2,ROW(Sheet1!$A$2:$A$1000)-ROW($A$2)+1,""),COLUMNS($A:A))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    02-04-2015
    Location
    India
    MS-Off Ver
    365 (2202)
    Posts
    226

    Re: Multiple match for single company

    Hi John,

    FANTASTIC way of doing this.... This was the output I was actually looking for.

    I'll do some more analysis with my raw data (huge data) and get back to you.

    Meanwhile !! Thanks so much !!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to wisely match company name to payment ref number
    By Rafa100 in forum Excel General
    Replies: 5
    Last Post: 03-17-2017, 04:06 AM
  2. match single cell across multiple sheets?
    By JMB10101 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-13-2016, 01:23 PM
  3. [SOLVED] Count of Multiple criteria with single word match
    By sswapnill in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-27-2012, 02:22 AM
  4. Convert multiple rows for a company to a single row
    By Chloe13 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-08-2012, 09:55 AM
  5. Get a list of multiple names that match a single criteria in another row
    By labtech in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-01-2012, 07:43 PM
  6. Calculating 401k Company Match up to 6%
    By tecumseh in forum Excel General
    Replies: 2
    Last Post: 01-15-2008, 09:58 PM
  7. [SOLVED] formula to calculate a 401K company match?
    By Trish in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-18-2006, 02:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1