Unique list with additional criteria

[jasonwdillon]

Hello,

Been searching high and low for an answer to my question with little luck (hoping i didn't blindly miss it).

I have a set of data which changes each week and need to extract a unique list from it, i currently have a formula which works for me, but i now need to adapt it to include additional criteria.

Current formula: {=IFERROR(INDEX($L$3:$L$77, MATCH(0,IF(ISBLANK($L$3:$L$77),1,COUNTIF($P$3:P3, $L$3:$L$77)), 0)),"")}

I need to include the condition that the cell in column I is blank.

So basically the unique list will NOT include any entries from L if the associated cell in I is NOT blank.

A friend helped me construct the above formula, i have some excel knowledge and vaguely know functions which would work, but everywhere i seem to apply them in the formula fails to do as i hoped.

 

[Aladin Akyurek]

Where do you want the formula?

 

[hiker95]

jasonwdillon,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, PNG file, or, flat text) try one of the following:

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


The following is a free site:

Or, you can upload your workbook to (the BLUE link-->) Box Net ,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[jasonwdillon]

Ideally on the same sheet (currently the list is in column P), however the location where the list comes out isn't too important so long as i can see it

 

[jasonwdillon]

Excel 2013

Windows 7

PC

Sample of data:

	B	C	D	E	F	G	H	I	J	K	L
20	402	1	0		04/08/2015	2	06/08/2015			50	A1
21	403	1	0	Bed & Breakfast	04/08/2015	1	05/08/2015				A2
22	404	2	0	Bed & Breakfast	04/08/2015	3	07/08/2015	XXX			A3
23	405	2	0	Bed & Breakfast	04/08/2015	3	07/08/2015	XXX			A3
24	406	1	0	Bed & Breakfast	04/08/2015	2	06/08/2015			51	A4
25	407	1	0	Conference Direct	02/08/2015	3	05/08/2015	XXX		52
26	408	1	0	Bed & Breakfast	03/08/2015	4	07/08/2015			58	A5
27	409	1	0	Conference Direct	02/08/2015	3	05/08/2015	YYY		54
28	410	1	0		03/08/2015	3	06/08/2015			53	A6
29	411	1	0		04/08/2015	2	06/08/2015			53	A6
30	412	1	0	Bed & Breakfast	03/08/2015	2	05/08/2015			59	A1
31	413	1	0	Conference Direct	19/07/2015	20	08/08/2015	ZZZ
32	414	1	0	Conference Direct	02/08/2015	6	08/08/2015	ZZZ
33	415	1	0	Conference Direct	19/07/2015	20	08/08/2015	ZZZ

<tbody>
</tbody>

Expected result:

P

A1

A2

A4

A5

A6

<tbody>
</tbody>

Hopefully this will suffice, please let me know if you need any more information.

As i stated above it is not essential that the results appear in Column P, that's just how I have been working at the moment.

 

[Aladin Akyurek]

Ideally on the same sheet (currently the list is in column P), however the location where the list comes out isn't too important so long as i can see it

Assuming that column L of data houses solely text values and Sheet1 is the sheet where the data is located (Adjust to suit.)

Activate Formulas | Name Manager and define the following dynamic named ranges...

Lrow:

=MATCH(REPT("z",Sheet1!$L:$L)

Lrange:

=Sheet1!$L$3:INDEX(Sheet1!$L:$L,Lrow)

Irange:

=Sheet1!$I$3:INDEX(Sheet1!$I:$I,Lrow)

And finally, Ivec:

=ROW(Lrange)-ROW(INDEX(Lrange,1,1))+1

P1, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(Lrange<>"",IF(Irange="",
      MATCH(Lrange,Lrange,0))),Ivec),1))

P2, unique list

P3, control+shift+enter, not just enter, and copy down:

=IF(ROWS($P$3:P3)<=$P$1,INDEX(Lrange,SMALL(IF(FREQUENCY(IF(Lrange<>"",
    IF(Irange="",MATCH(Lrange,Lrange,0))),Ivec),Ivec),ROWS($P$3:P3))),"")

 

[hiker95]

jasonwdillon,

Thanks for the screenshot.

Here is a macro solution for you to consider, that will adjust to the varying number of raw data rows in column B. And, that will run in the active worksheet, with the raw data beginning in cell B20, and, the results will be written to column P, beginning in cell P20.

If the raw data worksheet name is always the same, then I can adjust the macro.

Sample raw data, and, results:

Excel 2007
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
19
20	402	1	0		4/8/2015	2	6/8/2015			50	A1				A1
21	403	1	0	Bed & Breakfast	4/8/2015	1	5/8/2015				A2				A2
22	404	2	0	Bed & Breakfast	4/8/2015	3	7/8/2015	XXX			A3				A4
23	405	2	0	Bed & Breakfast	4/8/2015	3	7/8/2015	XXX			A3				A5
24	406	1	0	Bed & Breakfast	4/8/2015	2	6/8/2015			51	A4				A6
25	407	1	0	Conference Direct	2/8/2015	3	5/8/2015	XXX		52
26	408	1	0	Bed & Breakfast	3/8/2015	4	7/8/2015			58	A5
27	409	1	0	Conference Direct	2/8/2015	3	5/8/2015	YYY		54
28	410	1	0		3/8/2015	3	6/8/2015			53	A6
29	411	1	0		4/8/2015	2	6/8/2015			53	A6
30	412	1	0	Bed & Breakfast	3/8/2015	2	5/8/2015			59	A1
31	413	1	0	Conference Direct	19/07/2015	20	8/8/2015	ZZZ
32	414	1	0	Conference Direct	2/8/2015	6	8/8/2015	ZZZ
33	415	1	0	Conference Direct	19/07/2015	20	8/8/2015	ZZZ
34
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub jasonwdillon()
' hiker95, 08/12/2015, ME875071
Application.ScreenUpdating = False
Dim rng As Range, c As Range, p, lrb As Long
lrb = Cells(Rows.Count, "B").End(xlUp).Row
Set rng = Range("I20:I" & lrb)
With CreateObject("Scripting.Dictionary")
  For Each c In rng
    If c = vbEmpty Then
      If Not .Exists(c.Offset(, 3).Value) Then
        .Add c.Offset(, 3).Value, c.Offset(, 3).Value
      End If
    End If
  Next
  p = Application.Transpose(Array(.Keys))
End With
If lrb > 19 Then Range("P20:P" & lrb).ClearContents
Range("P20").Resize(UBound(p)) = p
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the jasonwdillon macro.

 

[jasonwdillon]

Sorry if this is me being dense,

Is this missing an end? It wont let me save the function to a name without completing the MATCH funct

=MATCH(REPT("z",Sheet1!$L:$L)

 

[jasonwdillon]

Hmm hoped for an edit button after realizing i didn't quote.

Lrow:

=MATCH(REPT("z",Sheet1!$L:$L)

is what i meant to send

 

[Aladin Akyurek]

Hmm hoped for an edit button after realizing i didn't quote.



is what i meant to send

Sorry about that. It's:

=MATCH(REPT("z",255),Sheet1!$L:$L)