Uninterrupted list with INDEX/MATCH

sous2817

Well-known Member

Joined

Feb 22, 2008

Messages

2,276

• Sep 12, 2008

• #2

Is there a value in B2? From what you've posted, the value returned would be 0 since there isn't anything there.

You could use =IF(B2=D2,IF(A2="","BLANK",A2)) changing "BLANK" to be whatever you want it to be.

 

Upvote 0

D

dafan

Well-known Member

Joined

May 6, 2008

Messages

692

• Sep 12, 2008

• #3

No you don't get it.

I want to have returned Mike in E2, Jason in E3. No blanks or any other value whatsoever.

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Sep 12, 2008

• #4

Could we use some helper columns like this?

1. E1 houses a 0.

2. E2 & G2 formulas copied down.

3. Columns E:F could be hidden if required. Also any pair of available columns could be used instead of columns E:F.

Excel Workbook
	A	B	C	D	E	F	G
1	Name	Group		Group	0	2	Name
2		A		A	0		Mike
3	Mike	A		A	1		Jason
4		A		A	1
5	Jason	A		A	2
6
Sheet1

 

Upvote 0

sous2817

Well-known Member

Joined

Feb 22, 2008

Messages

2,276

• Sep 12, 2008

• #5

If you data follows the same pattern as you have posted above, does

=IF(B2=D2,IF(A2="",A3,A2)) and copied down work? It seemed to in my test:

Book1
	A	B	C	D	E
1	Name	Group		Group	Name
2		A		A	Mike
3	Mike	A		A	Mike
4		A		A	John
5	John	A		A	John
6		A		A	Chris
7	Chris	A		A	Chris
8		A		A	Adam
9	Adam	A		A	Adam
10		A		A	Steve
11	Steve	A		A	Steve
Sheet1

 

Upvote 0

D

dafan

Well-known Member

Joined

May 6, 2008

Messages

692

• Sep 12, 2008

• #6

Peter:
Thanks! I got rid of F1 and used COUNTIF($B$2:$B$5,$D$2) instead. Can we get rid of E:E in some way? I'm not 100% sure what it does in your formula either.

Scous:
Thanks for the input but it is not what i am looking for. I can use formulas like yours ofc, but what i want is what Peter gave.

 

Upvote 0

D

dafan

Well-known Member

Joined

May 6, 2008

Messages

692

• Sep 12, 2008

• #7

Did you just delete a post?

I've noticed that arrays work too

Code:

=IF(ROWS(F$2:F2)>COUNTIF($B$2:$B$5,$D$2),"",INDEX(A:A,MATCH(ROWS(F$2:F2),{0,0,1,1,2},0)))

Can we get the results in E:E in 1 array formula?

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Sep 12, 2008

• #8

dafan said:

I got rid of F1 and used COUNTIF($B$2:$B$5,$D$2) instead.

Click to expand...

Where did you put this and how did it help? Could you post a screen shot to show?

In your sample, every row has the same thing in col B and D. Is this always true? If so, the answer changes considerably.

 

Upvote 0

D

dafan

Well-known Member

Joined

May 6, 2008

Messages

692

• Sep 12, 2008

• #9

Because you only need the count of the amount of non-empty matches.

Excel Workbook
	D	E	F
1	Group	0	Name
2	A	0	Mike
3	A	1	Jason
4	A	1
5	A	2
Sheet1

 

Upvote 0

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Sep 12, 2008

• #10

Well, you've just made those formulas more complex than they need be, and therefore using up more resources. If you don't like cell F1 or column E, just hide them. Or put them in columns Y and Z and hide them if you really want your list in column E.

Also, you didn't answer my other question. Is this really just a question of getting a list of the names in column A, or would some of the names be omitted from the list because their column B & D values do not match. If you want a good and efficient solution, we need to know the full circumstances.

 

Upvote 0