Can't even figure out which function to use here?

[rooirokbokkie]

This problem has me stumped because I can't figure out which function to use. This is a basic problem where I have a group of people who were supposed to bring items from a list/array called "Item List" for a cooking class. I need an easy way to compare what they "Brought" to the "Item List" for each individual so I can quickly see what I'll need to give them so they can participate. The flat structure of the data is tripping me up.

I'd prefer non-VBA answers, but VBA answers are always welcome since they help me learn as well. Thanks in advance.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Brought[/TD]
[TD]Item List[/TD]
[/TR]
[TR]
[TD]Pete[/TD]
[TD]Eggs[/TD]
[TD]Egg[/TD]
[/TR]
[TR]
[TD]Pete[/TD]
[TD]Flour[/TD]
[TD]Milk[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Milk[/TD]
[TD]Flour[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Egg[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Flour[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Flour[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Egg[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Milk[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Milk[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

What is the output that you desire?

 

[sheetspread]

Will the countif function help?

 

[rooirokbokkie]

What is the output that you desire?

Hi Aladin,

Output is not a major concern, but I want to clearly see what each person is missing from the "Item List"

So a list of which items each person is missing would be ideal.

 

[rooirokbokkie]

This is sort of what I'm looking for:

Name Needs
Pete Milk
John Egg
John Milk
Mary Flour

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr][tr][td]

1​

[/td][td]Name[/td][td]Brought[/td][td]Item List[/td][td][/td][td]Name[/td][td]Missing[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]Pete[/td][td]Egg[/td][td]Egg[/td][td][/td][td]Jane[/td][td]Missing: 0[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td]Pete[/td][td]Flour[/td][td]Flour[/td][td][/td][td]John[/td][td]Missing: 2[/td][td]Egg[/td][td]Milk[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]Jane[/td][td]Milk[/td][td]Milk[/td][td][/td][td]Mary[/td][td]Missing: 1[/td][td]Flour[/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]Jane[/td][td]Egg[/td][td][/td][td][/td][td]Pete[/td][td]Missing: 1[/td][td]Milk[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]Jane[/td][td]Flour[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]John[/td][td]Flour[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]Mary[/td][td]Egg[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]Mary[/td][td]Milk[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]Mary[/td][td]Milk[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In F2 control+shift+enter, not just enter, and copy down:

="Missing: "&SUM(IF(COUNTIFS($A$2:$A$10,E2,$B$2:$B$10,$C$2:$C$4)=0,1))

In G2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($C$2:$C$4,SMALL(IF(ISNA(MATCH($C$2:$C$4,IF($A$2:$A$10=$E2,$B$2:$B$10),0)),ROW($C$2:$C$4)-ROW($C$2)+1),COLUMNS($G2:G2))),"")

Is this something you can work with?

 

[jtakw]

Hi,

Another suggestion:

Excel 2010
	A	B	C	D	E	F	G
1	Name	Brought	Item List	Pete	Jane	John	Mary
2	Pete	Egg	Egg			Need
3	Pete	Flour	Milk	Need		Need
4	Jane	Milk	Flour				Need
5	Jane	Egg
6	Jane	Flour
7	John	Flour
8	Mary	Egg
9	Mary	Milk
10	Mary	Milk
Sheet16

Cell Formulas
Range	Formula
D2	=IF(COUNTIFS($A$2:$A$10,D$1,$B$2:$B$10,$C2)=0,"Need","")

D1:G1 names manually entered, D2 formula copied down and across.

 

[TotallyConfused]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Name[/TD]
[TD]Brought[/TD]
[TD]Item List[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Missing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Pete[/TD]
[TD]Egg[/TD]
[TD]Egg[/TD]
[TD][/TD]
[TD]Jane[/TD]
[TD]Missing: 0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Pete[/TD]
[TD]Flour[/TD]
[TD]Flour[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Missing: 2[/TD]
[TD]Egg[/TD]
[TD]Milk[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Jane[/TD]
[TD]Milk[/TD]
[TD]Milk[/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD]Missing: 1[/TD]
[TD]Flour[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Jane[/TD]
[TD]Egg[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pete[/TD]
[TD]Missing: 1[/TD]
[TD]Milk[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Jane[/TD]
[TD]Flour[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]John[/TD]
[TD]Flour[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Mary[/TD]
[TD]Egg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Mary[/TD]
[TD]Milk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Mary[/TD]
[TD]Milk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In F2 control+shift+enter, not just enter, and copy down:

="Missing: "&SUM(IF(COUNTIFS($A$2:$A$10,E2,$B$2:$B$10,$C$2:$C$4)=0,1))

In G2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($C$2:$C$4,SMALL(IF(ISNA(MATCH($C$2:$C$4,IF($A$2:$A$10=$E2,$B$2:$B$10),0)),ROW($C$2:$C$4)-ROW($C$2)+1),COLUMNS($G2:G2))),"")

Is this something you can work with?

Hello Aladin

I certainly do not want to divert this thread to anything else, but I have a question for you. What did you have to do in order to get all the data listed above here, to be displayed in such a nice, neat set of individual boxes? I’ve seen others do this, but never could figure how they managed to accomplish this feat. The only way I’ve been able to show some data, is to try to manually line everything up in rows and columns, which may or not always work.

Thanks in advance for your help.

TotallyConfused

 

[Joe4]

I certainly do not want to divert this thread to anything else, but I have a question for you. What did you have to do in order to get all the data listed above here, to be displayed in such a nice, neat set of individual boxes? I’ve seen others do this, but never could figure how they managed to accomplish this feat. The only way I’ve been able to show some data, is to try to manually line everything up in rows and columns, which may or not always work.

See: https://www.mrexcel.com/forum/about-board/508133-attachments.html

If you have any other questions regarding it, please post them in a new thread in the "About This Board" forum, so this thread doesn't end up being "hijacked".