Extract Unique Values from multiple columns

A

acemali

New Member
Joined
Jan 19, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Hi, this is my first question so I hope I didn't break any rules.

Anyway, I have 2 Tabs. Report and Raw Table.

In Raw_Table I have several columns with different values. I need to extract only the unique combined values from this column (Send-out, Segment, Keyword) in Report Tab in B4:B6 till last found unique value. For Example, I can have the same date, same segment but a different keyword and that means it needs to be extracted in another column as a unique value.

I know how to do this in google sheets, but in Office 2019 which doesn't have functions like unique/filter I have no idea even how to approach this.

I don't even know if this is possible to be done by formulas or best is with VBA?

Thank you for any help.
Report Tab
Example1.xlsx
ABCD
2Example of what I need from Raw_Data Tab
3
4Send-out2020-10-122020-09-28…
5Segmentsegjewelrysegappandacc…
6Keywordcontemporarybriolettexxlbead…
7Total Delivered
8Opens
9Open Rate %
10Unique Opens
11Unique Open Rate %
12Clicks
13Click Rate %
14Unique Clicks
15Unique Click Rate %
16Click-to-Open Rate %
17Unique Click-to-Open Rate %
18E-com Users
19E-com Sessions
20E-com Transactions
21E-com Conversion Rate %
22Avg. Revenue per Transaction
23Total Revenue €
Report

Raw Data
Example1.xlsx
ABCDEF
1Send-outLanguage/AreaEcom/Non EcomSegmentKeyword3rd
22020-10-12enEcommercesegjewelrycontemporaryglobal
32020-10-12deEcommercesegjewelrycontemporaryglobal
42020-10-12esEcommercesegjewelrycontemporaryglobal
52020-10-12frEcommercesegjewelrycontemporaryglobal
62020-10-12jpEcommercesegjewelrycontemporaryglobal
72020-10-12cnEcommercesegjewelrycontemporaryglobal
82020-10-12enNon Ecommercesegjewelrycontemporaryglobal
92020-10-12deNon Ecommercesegjewelrycontemporaryglobal
102020-10-12esNon Ecommercesegjewelrycontemporaryglobal
112020-10-12frNon Ecommercesegjewelrycontemporaryglobal
122020-10-12jpNon Ecommercesegjewelrycontemporaryglobal
132020-10-12cnNon Ecommercesegjewelrycontemporaryglobal
142020-09-28enEcommercesegappandaccbriolettexxlbeadglobal
152020-09-28deEcommercesegappandaccbriolettexxlbeadglobal
162020-09-28esEcommercesegappandaccbriolettexxlbeadglobal
172020-09-28itEcommercesegappandaccbriolettexxlbeadglobal
182020-09-28jpEcommercesegappandaccbriolettexxlbeadglobal
192020-09-28cnEcommercesegappandaccbriolettexxlbeadglobal
202020-09-28enNon Ecommercesegappandaccbriolettexxlbeadglobal
212020-09-28deNon Ecommercesegappandaccbriolettexxlbeadglobal
222020-09-28esNon Ecommercesegappandaccbriolettexxlbeadglobal
232020-09-28itNon Ecommercesegappandaccbriolettexxlbeadglobal
242020-09-28jpNon Ecommercesegappandaccbriolettexxlbeadglobal
252020-09-28cnNon Ecommercesegappandaccbriolettexxlbeadglobal
262020-09-16enEcommercesegnailslaunchglobal
272020-09-16jpEcommercesegnailslaunchglobal
282020-09-16enNon Ecommercesegnailslaunchglobal
292020-09-16jpNon Ecommercesegnailslaunchglobal
302020-09-14enEcommercesegjewelryemeraldigniteglobal
312020-09-14deEcommercesegjewelryemeraldigniteglobal
322020-09-14esEcommercesegjewelryemeraldigniteglobal
332020-09-14frEcommercesegjewelryemeraldigniteglobal
342020-09-14jpEcommercesegjewelryemeraldigniteglobal
352020-09-14cnEcommercesegjewelryemeraldigniteglobal
362020-09-14enNon Ecommercesegjewelryemeraldigniteglobal
372020-09-14deNon Ecommercesegjewelryemeraldigniteglobal
382020-09-14esNon Ecommercesegjewelryemeraldigniteglobal
392020-09-14frNon Ecommercesegjewelryemeraldigniteglobal
402020-09-14jpNon Ecommercesegjewelryemeraldigniteglobal
412020-09-14cnNon Ecommercesegjewelryemeraldigniteglobal
422020-10-12jpEcommercesegnailsnecklaceglobal
432020-10-12cnEcommercesegnailsnecklaceglobal
442020-10-12enNon Ecommercesegnailsnecklaceglobal
452020-10-12deNon Ecommercesegnailsnecklaceglobal
462020-09-28jpEcommercesegappandaccbriolettexxlbeadglobal
472020-09-28cnEcommercesegappandaccbriolettexxlbeadglobal
482020-09-28enNon Ecommercesegappandaccbriolettexxlbeadglobal
492020-09-28deNon Ecommercesegappandaccbriolettexxlbeadglobal
Raw_Data
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Hi & welcome to MrExcel.
How about
+Fluff v2.xlsm
ABCDEFGHIJKLMNOPQ
1Send-outLanguage/AreaEcom/Non EcomSegmentKeyword3rd
212/10/2020enEcommercesegjewelrycontemporaryglobal12/10/202028/09/202016/09/202014/09/202012/10/2020   
312/10/2020deEcommercesegjewelrycontemporaryglobalsegjewelrysegappandaccsegnailssegjewelrysegnails   
412/10/2020esEcommercesegjewelrycontemporaryglobalcontemporarybriolettexxlbeadlaunchemeraldignitenecklace   
512/10/2020frEcommercesegjewelrycontemporaryglobal
612/10/2020jpEcommercesegjewelrycontemporaryglobal
712/10/2020cnEcommercesegjewelrycontemporaryglobal
812/10/2020enNon Ecommercesegjewelrycontemporaryglobal
912/10/2020deNon Ecommercesegjewelrycontemporaryglobal
1012/10/2020esNon Ecommercesegjewelrycontemporaryglobal
1112/10/2020frNon Ecommercesegjewelrycontemporaryglobal
1212/10/2020jpNon Ecommercesegjewelrycontemporaryglobal
1312/10/2020cnNon Ecommercesegjewelrycontemporaryglobal
1428/09/2020enEcommercesegappandaccbriolettexxlbeadglobal
1528/09/2020deEcommercesegappandaccbriolettexxlbeadglobal
1628/09/2020esEcommercesegappandaccbriolettexxlbeadglobal
1728/09/2020itEcommercesegappandaccbriolettexxlbeadglobal
1828/09/2020jpEcommercesegappandaccbriolettexxlbeadglobal
1928/09/2020cnEcommercesegappandaccbriolettexxlbeadglobal
2028/09/2020enNon Ecommercesegappandaccbriolettexxlbeadglobal
2128/09/2020deNon Ecommercesegappandaccbriolettexxlbeadglobal
2228/09/2020esNon Ecommercesegappandaccbriolettexxlbeadglobal
2328/09/2020itNon Ecommercesegappandaccbriolettexxlbeadglobal
2428/09/2020jpNon Ecommercesegappandaccbriolettexxlbeadglobal
2528/09/2020cnNon Ecommercesegappandaccbriolettexxlbeadglobal
2616/09/2020enEcommercesegnailslaunchglobal
2716/09/2020jpEcommercesegnailslaunchglobal
2816/09/2020enNon Ecommercesegnailslaunchglobal
2916/09/2020jpNon Ecommercesegnailslaunchglobal
3014/09/2020enEcommercesegjewelryemeraldigniteglobal
3114/09/2020deEcommercesegjewelryemeraldigniteglobal
3214/09/2020esEcommercesegjewelryemeraldigniteglobal
3314/09/2020frEcommercesegjewelryemeraldigniteglobal
3414/09/2020jpEcommercesegjewelryemeraldigniteglobal
3514/09/2020cnEcommercesegjewelryemeraldigniteglobal
3614/09/2020enNon Ecommercesegjewelryemeraldigniteglobal
3714/09/2020deNon Ecommercesegjewelryemeraldigniteglobal
3814/09/2020esNon Ecommercesegjewelryemeraldigniteglobal
3914/09/2020frNon Ecommercesegjewelryemeraldigniteglobal
4014/09/2020jpNon Ecommercesegjewelryemeraldigniteglobal
4114/09/2020cnNon Ecommercesegjewelryemeraldigniteglobal
4212/10/2020jpEcommercesegnailsnecklaceglobal
4312/10/2020cnEcommercesegnailsnecklaceglobal
4412/10/2020enNon Ecommercesegnailsnecklaceglobal
4512/10/2020deNon Ecommercesegnailsnecklaceglobal
4628/09/2020jpEcommercesegappandaccbriolettexxlbeadglobal
4728/09/2020cnEcommercesegappandaccbriolettexxlbeadglobal
4828/09/2020enNon Ecommercesegappandaccbriolettexxlbeadglobal
4928/09/2020deNon Ecommercesegappandaccbriolettexxlbeadglobal
50
List
Cell Formulas
RangeFormula
I2:P2I2=IFERROR(INDEX($A$2:$A$49,AGGREGATE(15,6,(ROW($A$2:$A$49)-ROW($A$2)+1)/(((ISNA(MATCH($A$2:$A$49,$H$2:H$2,0)))+(ISNA(MATCH($D$2:$D$49,$H$3:H$3,0)))+(ISNA(MATCH($E$2:$E$49,$H$4:H$4,0))))>0),1)),"")
I3:P3I3=IFERROR(INDEX($D$2:$D$49,AGGREGATE(15,6,(ROW($A$2:$A$49)-ROW($A$2)+1)/(((ISNA(MATCH($A$2:$A$49,$H$2:H$2,0)))+(ISNA(MATCH($D$2:$D$49,$H$3:H$3,0)))+(ISNA(MATCH($E$2:$E$49,$H$4:H$4,0))))>0),1)),"")
I4:P4I4=IFERROR(INDEX($E$2:$E$49,AGGREGATE(15,6,(ROW($A$2:$A$49)-ROW($A$2)+1)/(((ISNA(MATCH($A$2:$A$49,$H$2:H$2,0)))+(ISNA(MATCH($D$2:$D$49,$H$3:H$3,0)))+(ISNA(MATCH($E$2:$E$49,$H$4:H$4,0))))>0),1)),"")
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA to amend report format based on unique values
Replies
1
Views
34
JoeRooney
J
A
  • Solved
take amount from last row in listbox on form and put into next column
Replies
7
Views
192
Abdo
A
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
126
Peter_SSs
Peter_SSs
K
  • Solved
Two dates, two cells - How do I format the second cell to change colour after x amount of time has elapsed from the first date?
Replies
8
Views
120
Kikaiki
K
C
  • Question Question
Revenue Allocation by Week
Replies
3
Views
809
ChetanPuri
C

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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