Want to return a unique list of values from a column that contains an array formula

freeb1893 · Aug 22, 2023

I have a column where I'm doing some indexing / matching via an array formula

I'd like to, in another column, calculate a list of unique values from that resulting array formula column

The UNIQUE function doesn't seem to work since the column I'm trying to process the unique values from has that array formula working in it. I keep getting a #SPILL! error.

Any ideas how I can get that unique list of values?

Fluff · Aug 22, 2023

Can you post some sample data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

The SPILL error normally means you have non-empty cells in the way of the formula.

etaf · Aug 22, 2023

can you provide an example
to address an array you can use # to show its an array that you are evaluating

what else is in the column you have Spill usually means contents in the cells the array is trying to populate

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

freeb1893 · Aug 22, 2023

Fluff said:
Can you post some sample data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

The SPILL error normally means you have non-empty cells in the way of the formula.

This is the formula running in the column I'm trying to index the unique values from:

{=IF(ISNA(IF(ISERROR(SEARCH(",",INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)-1,12))),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)+1,12),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)-1,12))),"",IF(ISERROR(SEARCH(",",INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)-1,12))),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)+1,12),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)-1,12)))}

This is an example of the resulting data output from that formula for the 1st 21 rows, note that some results are blank, deliberately:

Doe, John

Doe, Jane

Smith, John

Fluff · Aug 22, 2023

Can you post the actual data & explain what you are trying to do.

freeb1893 · Aug 22, 2023

Fluff said:
Can you post the actual data & explain what you are trying to do.

This is a sample of the array I'm indexing originally from the Raw Data worksheet:

LABEL

PKEYNUM

SUMMARY

PNAME

PNAME_1

CFNAME

NUMBERVALUE

CREATED

UPDATED

RESOLUTIONDATE

REPORTER

ASSIGNEE

CURRENT STATUS

CHANGE AUTHOR

CHANGE DATE

FIELD CHANGED

OLDSTRING

NEWSTRING

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

4/28/2023 10:15 AM

assignee

Doe, John

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

4/28/2023 10:15 AM

assignee

Doe, John

Doe, Jane

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

4/28/2023 10:15 AM

assignee

Doe, Jane

Doe, John

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

4/28/2023 10:16 AM

status

Ready to Develop

Development

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

5/3/2023 3:50 PM

status

Development

Ready for Code Review

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

5/3/2023 3:50 PM

assignee

Doe, John

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

5/9/2023 10:52 AM

assignee

Doe, Jane

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

5/9/2023 1:08 PM

status

Merge

Merge Done

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

5/9/2023 1:08 PM

assignee

Doe, Jane

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

6/1/2023 7:57 AM

priority

Critical

External Block

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

6/1/2023 8:01 AM

priority

External Block

Blocker

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

6/26/2023 11:15 AM

priority

Blocker

External Block

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

8/14/2023 10:25 AM

status

Ready for Release

Completed

E2C

EDGE-13901

Story

Trivial

Story Points

1

6/27/2022 3:42 PM

#################

JIRAUSER30100

Cancelled

6/28/2022 7:51 AM

assignee

Palmer, Arnold

E2C

EDGE-13901

Story

Trivial

Story Points

1

6/27/2022 3:42 PM

#################

JIRAUSER30100

Cancelled

7/27/2022 12:51 PM

status

Gathering Requirements

Cancelled

E2C

EDGE-13901

Story

Trivial

Story Points

1

6/27/2022 3:42 PM

#################

JIRAUSER30100

Cancelled

7/27/2022 12:52 PM

status

Cancelled

E2C

EDGE-13901

Story

Trivial

Story Points

1

6/27/2022 3:42 PM

#################

JIRAUSER30100

Cancelled

11/8/2022 7:55 AM

status

Open

Cancelled

E2C

EDGE-13974

Story

Major

Story Points

1

7/13/2022 4:50 PM

8/14/2023 9:47 AM

Merge Done

10/4/2022 9:21 AM

assignee

Smith, Bob

E2C

EDGE-13974

Story

Major

Story Points

1

7/13/2022 4:50 PM

8/14/2023 9:47 AM

Merge Done

10/5/2022 8:27 AM

status

Ready to Develop

Development

E2C

EDGE-13974

Story

Major

Story Points

1

7/13/2022 4:50 PM

8/14/2023 9:47 AM

Merge Done

10/9/2022 7:01 PM

assignee

Smith, Bob

E2C

EDGE-13974

Story

Major

Story Points

1

7/13/2022 4:50 PM

8/14/2023 9:47 AM

Merge Done

10/9/2022 7:01 PM

status

Development

Ready for Code Review

E2C

EDGE-13974

Story

Major

Story Points

1

7/13/2022 4:50 PM

8/14/2023 9:47 AM

Merge Done

10/10/2022 9:12 AM

assignee

Smith, Steven

E2C

EDGE-13974

Story

Major

Story Points

1

7/13/2022 4:50 PM

8/14/2023 9:47 AM

Merge Done

10/10/2022 2:10 PM

status

Merge

Merge Done

E2C

EDGE-13974

Story

Major

Story Points

1

7/13/2022 4:50 PM

8/14/2023 9:47 AM

Merge Done

7/11/2023 8:56 AM

assignee

Smith, Steven

On the worksheet where I'm doing calculations from this data set, Column M is a unique list of values from from the above data set Column B. Here is an example of Column M on my current worksheet:

Unique Ticket List

Ticket Number

AT-33

EDGE-13283

EDGE-13293

EDGE-13645

EDGE-13900

EDGE-13901

EDGE-13904

EDGE-13905

EDGE-13906

EDGE-13907

EDGE-13908

EDGE-13909

EDGE-13910

EDGE-13911

EDGE-13952

EDGE-13974

EDGE-13985

EDGE-13987

EDGE-13998

This is my formula in Column M on my current worksheet:
=UNIQUE('RAW Data'!$B$2:$B$10000)

Then with this formula:
{=IF(ISNA(IF(ISERROR(SEARCH(",",INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)-1,12))),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)+1,12),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)-1,12))),"",IF(ISERROR(SEARCH(",",INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)-1,12))),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)+1,12),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)-1,12)))}

I am looking up my unique list of ticket numbers against the Raw Data worksheet array, matching on both the ticket number and if the value of "Development" is found in Column M of the Raw Data worksheet, then checking the row above or row below for a comma in Column M of the array, if the value has a comma in it, index it.

Here's the example output of that formula

AT-33	Development	4/28/23 10:16 AM	Doe, John
EDGE-13901
EDGE-13974	Development	10/5/22 8:27 AM	Smith, Bob

It's that resulting data set I'm trying to again index for a unique list of values.

There are times where the index will return blanks because the unique ticket number being indexed won't have a record in the original array on the Raw Data worksheet where Column M = "Development"

freeb1893 · Aug 22, 2023

Fluff said:
Can you post the actual data & explain what you are trying to do.

Sorry my time to edit expired. This has some edits and additional details:

This is a sample of the array I'm indexing originally from the Raw Data worksheet:

LABEL

PKEYNUM

SUMMARY

PNAME

PNAME_1

CFNAME

NUMBERVALUE

CREATED

UPDATED

RESOLUTIONDATE

REPORTER

ASSIGNEE

CURRENT STATUS

CHANGE AUTHOR

CHANGE DATE

FIELD CHANGED

OLDSTRING

NEWSTRING

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

4/28/2023 10:15 AM

assignee

Doe, John

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

4/28/2023 10:15 AM

assignee

Doe, John

Doe, Jane

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

4/28/2023 10:15 AM

assignee

Doe, Jane

Doe, John

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

4/28/2023 10:16 AM

status

Ready to Develop

Development

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

5/3/2023 3:50 PM

status

Development

Ready for Code Review

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

5/3/2023 3:50 PM

assignee

Doe, John

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

5/9/2023 10:52 AM

assignee

Doe, Jane

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

5/9/2023 1:08 PM

status

Merge

Merge Done

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

5/9/2023 1:08 PM

assignee

Doe, Jane

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

6/1/2023 7:57 AM

priority

Critical

External Block

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

6/1/2023 8:01 AM

priority

External Block

Blocker

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

6/26/2023 11:15 AM

priority

Blocker

External Block

E2C

AT-33

Story

External Block

Story Points

5

2/20/2023 9:14 AM

#################

Completed

8/14/2023 10:25 AM

status

Ready for Release

Completed

E2C

EDGE-13901

Story

Trivial

Story Points

1

6/27/2022 3:42 PM

#################

JIRAUSER30100

Cancelled

6/28/2022 7:51 AM

assignee

Palmer, Arnold

E2C

EDGE-13901

Story

Trivial

Story Points

1

6/27/2022 3:42 PM

#################

JIRAUSER30100

Cancelled

7/27/2022 12:51 PM

status

Gathering Requirements

Cancelled

E2C

EDGE-13901

Story

Trivial

Story Points

1

6/27/2022 3:42 PM

#################

JIRAUSER30100

Cancelled

7/27/2022 12:52 PM

status

Cancelled

E2C

EDGE-13901

Story

Trivial

Story Points

1

6/27/2022 3:42 PM

#################

JIRAUSER30100

Cancelled

11/8/2022 7:55 AM

status

Open

Cancelled

E2C

EDGE-13974

Story

Major

Story Points

1

7/13/2022 4:50 PM

8/14/2023 9:47 AM

Merge Done

10/4/2022 9:21 AM

assignee

Smith, Bob

E2C

EDGE-13974

Story

Major

Story Points

1

7/13/2022 4:50 PM

8/14/2023 9:47 AM

Merge Done

10/5/2022 8:27 AM

status

Ready to Develop

Development

E2C

EDGE-13974

Story

Major

Story Points

1

7/13/2022 4:50 PM

8/14/2023 9:47 AM

Merge Done

10/9/2022 7:01 PM

assignee

Smith, Bob

E2C

EDGE-13974

Story

Major

Story Points

1

7/13/2022 4:50 PM

8/14/2023 9:47 AM

Merge Done

10/9/2022 7:01 PM

status

Development

Ready for Code Review

E2C

EDGE-13974

Story

Major

Story Points

1

7/13/2022 4:50 PM

8/14/2023 9:47 AM

Merge Done

10/10/2022 9:12 AM

assignee

Smith, Steven

E2C

EDGE-13974

Story

Major

Story Points

1

7/13/2022 4:50 PM

8/14/2023 9:47 AM

Merge Done

10/10/2022 2:10 PM

status

Merge

Merge Done

E2C

EDGE-13974

Story

Major

Story Points

1

7/13/2022 4:50 PM

8/14/2023 9:47 AM

Merge Done

7/11/2023 8:56 AM

assignee

Smith, Steven

On the worksheet where I'm doing calculations from this data set, Column M is a unique list of values from from the above data set Column B. Here is an example of Column M on my current worksheet:

Unique Ticket List

AT-33

EDGE-13901

EDGE-13974

EDGE-13985

EDGE-14024

EDGE-14039

EDGE-14065

EDGE-14074

EDGE-14139

EDGE-14240

EDGE-14286

EDGE-14289

EDGE-14306

EDGE-14310

EDGE-14311

EDGE-14349

EDGE-14352

EDGE-14378

This is my formula in Column M on my current worksheet:
=UNIQUE('RAW Data'!$B$2:$B$10000)

Then with this formula:
{=IF(ISNA(IF(ISERROR(SEARCH(",",INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)-1,12))),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)+1,12),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)-1,12))),"",IF(ISERROR(SEARCH(",",INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)-1,12))),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)+1,12),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M10='RAW Data'!$B$2:$B$10000),0)-1,12)))}

I am looking up my unique list of ticket numbers against the Raw Data worksheet array, matching on both the ticket number and if the value of "Development" is found in Column M of the Raw Data worksheet, then checking the row above or row below for a comma in Column M of the array, if the value has a comma in it, index it. There's also some wrappers to give me blanks if it's N/A or errors out)

Here's the example output of that formula (the 4th column below)

AT-33	Development	4/28/23 10:16 AM	Doe, John
EDGE-13901
EDGE-13974	Development	10/5/22 8:27 AM	Smith, Bob

It's that resulting data set I'm trying to again index for a unique list of values.

There are times where the index will return blanks because the unique ticket number being indexed won't have a record in the original array on the Raw Data worksheet where Column M = "Development"

Fluff · Aug 22, 2023

As col M on your raw data doesn't have anything saying "Development" it's difficult to help, but if your array formula is in col P try

Excel Formula:

=unique(filter(p2:p100,p2:p100<>""))

freeb1893 · Aug 23, 2023

Fluff said:
As col M on your raw data doesn't have anything saying "Development" it's difficult to help, but if your array formula is in col P try

Excel Formula:

=unique(filter(p2:p100,p2:p100<>""))

Ahh sorry you're right, my apologies, I had 2 different versions of this Excel calculator up comparing results and they weren't apples to apples column-wise and I was jacking this up between the variations. I closed the other Excel workbook.

Let me try this again, I'm so sorry I was just swamped yesterday and really struggled to get the bandwidth to provide all this information as well as I needed to, and provide it well:

This is a sample of the array I'm indexing originally from the Raw Data worksheet:

LABEL

PKEYNUM

SUMMARY

PNAME

CREATED

UPDATED

RESOLUTIONDATE

CURRENT STATUS

CHANGE AUTHOR

CHANGE DATE

FIELD CHANGED

OLDSTRING

NEWSTRING

E2C

AT-33

TECH: Coordinate with AutoTools team to fix the modelYearId endpoint

Story

2/20/2023 9:14 AM

#################

Completed

4/28/2023 10:15 AM

assignee

Smith, John

E2C

AT-33

TECH: Coordinate with AutoTools team to fix the modelYearId endpoint

Story

2/20/2023 9:14 AM

#################

Completed

4/28/2023 10:15 AM

assignee

Smith, John

Williams, Jesse

E2C

AT-33

TECH: Coordinate with AutoTools team to fix the modelYearId endpoint

Story

2/20/2023 9:14 AM

#################

Completed

4/28/2023 10:15 AM

assignee

Williams, Jesse

Smith, John

E2C

AT-33

TECH: Coordinate with AutoTools team to fix the modelYearId endpoint

Story

2/20/2023 9:14 AM

#################

Completed

4/28/2023 10:16 AM

status

Ready to Develop

Development

E2C

AT-33

TECH: Coordinate with AutoTools team to fix the modelYearId endpoint

Story

2/20/2023 9:14 AM

#################

Completed

5/3/2023 3:50 PM

status

Development

Ready for Code Review

E2C

AT-33

TECH: Coordinate with AutoTools team to fix the modelYearId endpoint

Story

2/20/2023 9:14 AM

#################

Completed

5/3/2023 3:50 PM

assignee

Smith, John

E2C

AT-33

TECH: Coordinate with AutoTools team to fix the modelYearId endpoint

Story

2/20/2023 9:14 AM

#################

Completed

5/9/2023 10:52 AM

assignee

Williams, Jesse

E2C

AT-33

TECH: Coordinate with AutoTools team to fix the modelYearId endpoint

Story

2/20/2023 9:14 AM

#################

Completed

5/9/2023 1:08 PM

status

Merge

Merge Done

E2C

AT-33

TECH: Coordinate with AutoTools team to fix the modelYearId endpoint

Story

2/20/2023 9:14 AM

#################

Completed

5/9/2023 1:08 PM

assignee

Williams, Jesse

E2C

AT-33

TECH: Coordinate with AutoTools team to fix the modelYearId endpoint

Story

2/20/2023 9:14 AM

#################

Completed

6/1/2023 7:57 AM

priority

Critical

External Block

E2C

AT-33

TECH: Coordinate with AutoTools team to fix the modelYearId endpoint

Story

2/20/2023 9:14 AM

#################

Completed

6/1/2023 8:01 AM

priority

External Block

Blocker

E2C

AT-33

TECH: Coordinate with AutoTools team to fix the modelYearId endpoint

Story

2/20/2023 9:14 AM

#################

Completed

6/26/2023 11:15 AM

priority

Blocker

External Block

E2C

AT-33

TECH: Coordinate with AutoTools team to fix the modelYearId endpoint

Story

2/20/2023 9:14 AM

#################

Completed

8/14/2023 10:25 AM

status

Ready for Release

Completed

E2C

EDGE-13283

Vehicle payments batch jobs in TWS analysis (GM and Ford)

Story

3/28/2022 1:23 PM

3/15/2023 6:09 PM

Cancelled

3/28/2022 1:23 PM

assignee

Williams, Tom

E2C

EDGE-13283

Vehicle payments batch jobs in TWS analysis (GM and Ford)

Story

3/28/2022 1:23 PM

3/15/2023 6:09 PM

Cancelled

4/4/2022 8:35 PM

assignee

Williams, Tom

E2C

EDGE-13283

Vehicle payments batch jobs in TWS analysis (GM and Ford)

Story

3/28/2022 1:23 PM

3/15/2023 6:09 PM

Cancelled

5/26/2022 10:16 AM

status

Open

Cancelled

E2C

EDGE-13293

QUOTE DELETION batch jobs in TWS analysis

Story

3/29/2022 2:07 PM

3/15/2023 6:09 PM

Open

3/29/2022 2:07 PM

assignee

Williams, Tom

Gibbs, Harry

E2C

EDGE-13293

QUOTE DELETION batch jobs in TWS analysis

Story

3/29/2022 2:07 PM

3/15/2023 6:09 PM

Open

3/30/2022 8:41 AM

assignee

Gibbs, Harry

E2C

EDGE-13645

CAN_UPD_LICENSE

Epic

5/9/2022 4:28 PM

8/16/2023 8:45 AM

Cancelled

8/16/2023 8:45 AM

status

Open

Cancelled

E2C

EDGE-13900

Data Update Batch Jobs

User Story

6/27/2022 2:50 PM

#################

11/8/2022 7:55 AM

Cancelled

11/8/2022 7:55 AM

status

Open

Cancelled

E2C

EDGE-13901

Learn Batch job scheduling in TWS

Story

6/27/2022 3:42 PM

#################

Cancelled

6/28/2022 7:51 AM

assignee

Palmer, Arnold

E2C

EDGE-13901

Learn Batch job scheduling in TWS

Story

6/27/2022 3:42 PM

#################

Cancelled

7/27/2022 12:51 PM

status

Gathering Requirements

Cancelled

E2C

EDGE-13901

Learn Batch job scheduling in TWS

Story

6/27/2022 3:42 PM

#################

Cancelled

7/27/2022 12:52 PM

status

Cancelled

E2C

EDGE-13901

Learn Batch job scheduling in TWS

Story

6/27/2022 3:42 PM

#################

Cancelled

11/8/2022 7:55 AM

status

Open

Cancelled

E2C

EDGE-13904

Define the different types of batch jobs

Story

6/28/2022 7:47 AM

#################

Cancelled

6/28/2022 7:49 AM

assignee

Doe, Jane

E2C

EDGE-13904

Define the different types of batch jobs

Story

6/28/2022 7:47 AM

#################

Cancelled

6/28/2022 7:56 AM

assignee

Doe, Jane

Doe, John

E2C

EDGE-13904

Define the different types of batch jobs

Story

6/28/2022 7:47 AM

#################

Cancelled

6/30/2022 7:30 AM

status

Pre-Grooming

Cancelled

E2C

EDGE-13905

List migration options for each batch job type

Story

6/28/2022 7:55 AM

#################

Cancelled

7/6/2022 9:18 AM

priority

Major

External Block

E2C

EDGE-13905

List migration options for each batch job type

Story

6/28/2022 7:55 AM

#################

Cancelled

7/18/2022 8:03 AM

priority

External Block

Major

E2C

EDGE-13905

List migration options for each batch job type

Story

6/28/2022 7:55 AM

#################

Cancelled

7/18/2022 12:55 PM

status

Gathering Requirements

Cancelled

E2C

EDGE-13906

Validate if the List of Batch jobs is complete

Story

6/28/2022 7:59 AM

#################

Cancelled

11/8/2022 7:56 AM

status

Open

Cancelled

E2C

EDGE-13907

Validate the count of Long running jobs

Story

6/28/2022 8:00 AM

#################

Cancelled

7/8/2022 9:36 AM

assignee

Gibbs, Harry

E2C

EDGE-13907

Validate the count of Long running jobs

Story

6/28/2022 8:00 AM

#################

Cancelled

7/19/2022 9:16 AM

status

Gathering Requirements

Cancelled

E2C

EDGE-13907

Validate the count of Long running jobs

Story

6/28/2022 8:00 AM

#################

Cancelled

7/27/2022 10:53 AM

assignee

Gibbs, Harry

Williams, Tom

E2C

EDGE-13908

Identify common themes or patterns of Job failure

Story

6/28/2022 8:03 AM

#################

Cancelled

6/29/2022 10:24 AM

assignee

Williams, Tom

E2C

EDGE-13908

Identify common themes or patterns of Job failure

Story

6/28/2022 8:03 AM

#################

Cancelled

7/25/2022 11:00 AM

status

Gathering Requirements

Cancelled

E2C

EDGE-13908

Identify common themes or patterns of Job failure

Story

6/28/2022 8:03 AM

#################

Cancelled

7/26/2022 1:33 PM

status

Cancelled

E2C

EDGE-13908

Identify common themes or patterns of Job failure

Story

6/28/2022 8:03 AM

#################

Cancelled

7/27/2022 10:41 AM

status

Cancelled

E2C

EDGE-13908

Identify common themes or patterns of Job failure

Story

6/28/2022 8:03 AM

#################

Cancelled

7/27/2022 12:57 PM

status

Cancelled

E2C

EDGE-13909

Identify common actions or type of actions taken at the end of a job

Story

6/28/2022 8:05 AM

#################

Cancelled

6/29/2022 10:33 AM

assignee

Gibbs, Harry

E2C

EDGE-13909

Identify common actions or type of actions taken at the end of a job

Story

6/28/2022 8:05 AM

#################

Cancelled

7/5/2022 1:08 PM

status

Ready to Develop

Development

E2C

EDGE-13909

Identify common actions or type of actions taken at the end of a job

Story

6/28/2022 8:05 AM

#################

Cancelled

7/5/2022 1:08 PM

status

Development

Ready for Code Review

E2C

EDGE-13909

Identify common actions or type of actions taken at the end of a job

Story

6/28/2022 8:05 AM

#################

Cancelled

7/5/2022 1:08 PM

status

Merge

Merge Done

E2C

EDGE-13909

Identify common actions or type of actions taken at the end of a job

Story

6/28/2022 8:05 AM

#################

Cancelled

7/5/2022 1:09 PM

assignee

Gibbs, Harry

E2C

EDGE-13909

Identify common actions or type of actions taken at the end of a job

Story

6/28/2022 8:05 AM

#################

Cancelled

7/5/2022 1:28 PM

assignee

Gibbs, Harry

E2C

EDGE-13909

Identify common actions or type of actions taken at the end of a job

Story

6/28/2022 8:05 AM

#################

Cancelled

7/5/2022 1:29 PM

assignee

Gibbs, Harry

E2C

EDGE-13909

Identify common actions or type of actions taken at the end of a job

Story

6/28/2022 8:05 AM

#################

Cancelled

7/5/2022 1:29 PM

status

Merge Done

Cancelled

E2C

EDGE-13910

Identify common actions or type of actions before a job runs

Story

6/28/2022 8:06 AM

#################

Cancelled

7/5/2022 1:30 PM

assignee

Gibbs, Harry

E2C

EDGE-13910

Identify common actions or type of actions before a job runs

Story

6/28/2022 8:06 AM

#################

Cancelled

7/11/2022 9:15 AM

status

Gathering Requirements

Cancelled

E2C

EDGE-13911

Identify techniques used by jobs in wait mode like polling

Story

6/28/2022 8:09 AM

#################

Cancelled

8/2/2022 12:56 PM

status

Open

Cancelled

E2C

EDGE-13952

EDGE class structure analysis

Story

7/8/2022 10:34 AM

#################

Cancelled

7/8/2022 10:35 AM

assignee

Doe, John

E2C

EDGE-13952

EDGE class structure analysis

Story

7/8/2022 10:34 AM

#################

Cancelled

7/18/2022 9:24 AM

status

Gathering Requirements

Cancelled

On the worksheet where I'm doing calculations from this data set, Column M is a unique list of values from from the above data set Column B. Here is an example of Column M on my current worksheet:

Tickets starting Development

Ticket Number

AT-33

EDGE-13283

EDGE-13293

EDGE-13645

EDGE-13900

EDGE-13901

EDGE-13904

EDGE-13905

EDGE-13906

EDGE-13907

EDGE-13908

EDGE-13909

EDGE-13910

EDGE-13911

EDGE-13952

This is my formula in Column M on my current worksheet:
=UNIQUE('RAW Data'!$B$2:$B$10000)

Then with this formula in Column P of my current worksheet:
{=IF(ISNA(IF(ISERROR(SEARCH(",",INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M3='RAW Data'!$B$2:$B$10000),0)-1,12))),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M3='RAW Data'!$B$2:$B$10000),0)+1,12),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M3='RAW Data'!$B$2:$B$10000),0)-1,12))),"",IF(ISERROR(SEARCH(",",INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M3='RAW Data'!$B$2:$B$10000),0)-1,12))),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M3='RAW Data'!$B$2:$B$10000),0)+1,12),INDEX('RAW Data'!$B$2:$M$10000,MATCH(1,("Development"='RAW Data'!$M$2:$M$10000)*($M3='RAW Data'!$B$2:$B$10000),0)-1,12)))}

With that formula in Column P of my current worksheet, I'm looking up my unique list of Ticket Numbers against the Raw Data worksheet array, matching on both the Ticket Number and if a value of "Development" is found in Column M of the Raw Data worksheet, if both match, then checking the Column M value in the row above or row below the "Development" match for a comma, and if the value above or below has a comma in it, index that. (There's also some wrappers to give me blanks if it's N/A or errors out)

Here's the example output of that formula (the 4th column, Column P on my current worksheet below):

AT-33	Development	4/28/23 10:16 AM	Smith, John
EDGE-13283
EDGE-13293
EDGE-13645
EDGE-13900
EDGE-13901
EDGE-13904
EDGE-13905
EDGE-13906
EDGE-13907
EDGE-13908
EDGE-13909	Development	7/5/22 1:08 PM	Gibbs, Harry
EDGE-13910
EDGE-13911
EDGE-13952

It's that resulting data set in Column P of my current worksheet that I'm trying to again index, this time for a unique list of values from that column. Basically get that unique list of names that are indexed into Column P from the prior formula.

There are times where the index will return blanks because the unique ticket number being indexed won't have a record in the original array on the Raw Data worksheet where Column M = "Development". Those are the blanks in Column P of my current worksheet.

I tried your formula and if it's not an array, it still presents the SPILL error. If I make that formula an array formula, it eliminates the SPILL error, but it's resulting in duplicates of the non-"" values. This is the results currently if I make that an array formula:

Developer

Smith, John

Gibbs, Harry

Fluff · Aug 23, 2023

freeb1893 said:
I tried your formula and if it's not an array, it still presents the SPILL error.

That means you have cells below the formula that are not completely empty. You need to clear them.
Also with 365 you no longer need to array enter any formula.

Want to return a unique list of values from a column that contains an array formula

freeb1893

Board Regular

Excel Facts

Fluff

MrExcel MVP, Moderator

etaf

Well-known Member

XL2BB - Excel Range to BBCode

Test Here

freeb1893

Board Regular

Fluff

MrExcel MVP, Moderator

freeb1893

Board Regular

freeb1893

Board Regular

Fluff

MrExcel MVP, Moderator

freeb1893

Board Regular

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Want to return a unique list of values from a column that contains an array formula

Board Regular

Excel Facts

MrExcel MVP, Moderator

Well-known Member

Board Regular

MrExcel MVP, Moderator

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock