Extracting 2nd, 3rd (and so on) Values while ignoring Duplicates

L

LearnExcl

Board Regular
Joined
Mar 17, 2010
Messages
245
Office Version
  1. 2016
Platform
  1. Windows
Hello All, I need your assistance. See the tables below. So, I need to extract a unique list of vendor codes from the "DATA" below based on their area codes. Then, I need to sum the values under "Presence" corresponding to the vendor codes. The cell (B1) next to "Area Code" below is where I would enter the area code for which I would like to extract a unique list of vendor codes as well as their presence.The result of the area code SADF is illustrated below. I very much appreciate your time and effort in helping me out.
[TABLE="width: 223"]
<tbody>[TR]
[TD="colspan: 3"]DATA
Sheet1
A B C[/TD]
[/TR]
[TR]
[TD]Area Code[/TD]
[TD]Vendor Code[/TD]
[TD]Presence[/TD]
[/TR]
[TR]
[TD]SADF[/TD]
[TD]1F090[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]SADF[/TD]
[TD]1F090[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]SADF[/TD]
[TD]1F090[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]SADF[/TD]
[TD]2N031[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]SADF[/TD]
[TD]9D001[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]SADF[/TD]
[TD]2N031[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]SADF[/TD]
[TD]9D001[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]SADF[/TD]
[TD]1F090[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]FADR[/TD]
[TD]1F090[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]FADR[/TD]
[TD]1F090[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]FADR[/TD]
[TD]4R023[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]FADR[/TD]
[TD]1F090[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CAER[/TD]
[TD]4R023[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]CAER[/TD]
[TD]5TA90[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]CAER[/TD]
[TD]5TA90[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CAER[/TD]
[TD]2S0TY[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

[TABLE="width: 176"]
<tbody>[TR]
[TD="colspan: 2"]RESULT
Sheet 2
A B[/TD]
[/TR]
[TR]
[TD]Area Code:[/TD]
[TD]SADF[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Vendor Code[/TD]
[TD]Presence[/TD]
[/TR]
[TR]
[TD]1F090[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]2N031[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]9D001[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody><colgroup><col span="2"></colgroup>[/TABLE]
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
maybe something like....

Excel 2016 (Windows) 32 bit[TABLE="class: grid, width: 400"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Area Code[/TD]
[TD]Vendor Code[/TD]
[TD]Presence[/TD]
[TD="align: right"][/TD]
[TD]Area Code:[/TD]
[TD]SADF[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]SADF[/TD]
[TD]1F090[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD]Vendor Code[/TD]
[TD]Presence[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]SADF[/TD]
[TD]1F090[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD]1F090[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]SADF[/TD]
[TD]1F090[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD]2N031[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]SADF[/TD]
[TD]2N031[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9D001[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]SADF[/TD]
[TD="align: right"]9D001[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]SADF[/TD]
[TD]2N031[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]SADF[/TD]
[TD="align: right"]9D001[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]SADF[/TD]
[TD]1F090[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]FADR[/TD]
[TD]1F090[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]FADR[/TD]
[TD]1F090[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]FADR[/TD]
[TD]4R023[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]FADR[/TD]
[TD]1F090[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]CAER[/TD]
[TD]4R023[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]CAER[/TD]
[TD]5TA90[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]CAER[/TD]
[TD]5TA90[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]CAER[/TD]
[TD]2S0TY[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=IF(E3="","",SUMIFS(Sheet1!$C$2:$C$17,Sheet1!$A$2:$A$17,$F$1,Sheet1!$B$2:$B$17,E3))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$17,MATCH(0,IF(Sheet1!$A$2:$A$17=$F$1,COUNTIF($E$2:E2,Sheet1!$B$2:$B$17)),0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Weazel said:
maybe something like....

Excel 2016 (Windows) 32 bit[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Area Code
[/TD]
[TD]Vendor Code
[/TD]
[TD]Presence
[/TD]
[TD="align: right"][/TD]
[TD]Area Code:
[/TD]
[TD]SADF
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]SADF
[/TD]
[TD]1F090
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD]Vendor Code
[/TD]
[TD]Presence
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]SADF
[/TD]
[TD]1F090
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]1F090
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]SADF
[/TD]
[TD]1F090
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD]2N031
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]SADF
[/TD]
[TD]2N031
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9D001
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]SADF
[/TD]
[TD="align: right"]9D001
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]SADF
[/TD]
[TD]2N031
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]SADF
[/TD]
[TD="align: right"]9D001
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]SADF
[/TD]
[TD]1F090
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD]FADR
[/TD]
[TD]1F090
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD]FADR
[/TD]
[TD]1F090
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD]FADR
[/TD]
[TD]4R023
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD]FADR
[/TD]
[TD]1F090
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]14
[/TD]
[TD]CAER
[/TD]
[TD]4R023
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]15
[/TD]
[TD]CAER
[/TD]
[TD]5TA90
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]16
[/TD]
[TD]CAER
[/TD]
[TD]5TA90
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]17
[/TD]
[TD]CAER
[/TD]
[TD]2S0TY
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH]F3
[/TH]
[TD="align: left"]=IF(E3="","",SUMIFS(Sheet1!$C$2:$C$17,Sheet1!$A$2:$A$17,$F$1,Sheet1!$B$2:$B$17,E3))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH]E3
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$17,MATCH(0,IF(Sheet1!$A$2:$A$17=$F$1,COUNTIF($E$2:E2,Sheet1!$B$2:$B$17)),0)),"")}
[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Thank you so much for your response. I need an additional huge favor though. In addition to the Area Code, if I wanted to control for another criterion, how would I go about doing that? For instance, under column D, there is a list of values reflecting access codes (ranging from 101 to 106). Can we insert "Access Code" in G1 and then enter 101 - 106 in cells G2 - G7 and control for them that way? Keep in mind, there may be a need to control for only 1 or up to 6 access codes at a time. Your help will be greatly appreciated.
 
Upvote 0
Hi Weasel. Would you be so kind as to explain the MATCH(0,IF(Sheet1!$A$2:$A$17=$F$1,COUNTIF($E$2:E2,Sheet1!$B$2:$B$17)),0) portion of the formula? I really appreciate your time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Excel Function to Extract Text String, Remove Duplicate & Count
Replies
5
Views
280
arijitirf
A
ExcelNewbie2020
  • Solved
Extract unique values and sum duplicates
Replies
3
Views
240
alansidman
A
M
  • Question Question
Load Array extract data based on two filters and copy to new sheet
Replies
3
Views
796
moorecurls
M
H
  • Question Question
VBA to extract data from Excel database sheet based on cell value on activesheet
Replies
1
Views
1K
Hunter_001
H
N
  • Solved
Extracting a range of non-contiguous cells from either of Two sheets in a folder of worksheets
Replies
5
Views
944
Alex Blakenburg
A

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top