Populating the sheets with data from another sheet when criteria is met

[Lifecoachlee]

Hi everyone,I need some help here.

Here's my table.

COLUMN A	COLUMN B	COLUMN C
DEALER CODE	LOCATION	RESULT
ASMART PBAKEXMILL ACAPARROS ASMKTG AMSERCO ASIATIC H-IIXMILL CSC CALOSUY DAETSLK EGSMDSE	ARIAS WHSE ARIAS WHSE METRO MANILA ARIAS WHSE PROVINCE ARIAS WHSE ARIAS WHSE METRO MANILA ARIAS WHSE PROVINCE PROVINCE

<tbody>
</tbody>

Is there any formula in excel where you can populate the cells in Column C all dealers which are from Metro Manila and Province?


Thank you for the help.


Many thanks,
Lee

 

[Aladin Akyurek]

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

=IFERROR(INDEX($A$3:$A$13,
  SMALL(IF(ISNUMBER(MATCH($B$3:$B$13,{"metro manila","province"},0)),
  ROW($B$3:$B$13)-ROW($B$3)+1),ROWS($C$3:C3))),"")

 

[Lifecoachlee]

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

=IFERROR(INDEX($A$3:$A$13,
  SMALL(IF(ISNUMBER(MATCH($B$3:$B$13,{"metro manila","province"},0)),
  ROW($B$3:$B$13)-ROW($B$3)+1),ROWS($C$3:C3))),"")

Sir, you're a genius!!!
God bless you, brother!

 

[Aladin Akyurek]

Sir, you're a genius!!!
God bless you, brother!

You're welcome. Glad to help.

 

[Lifecoachlee]

You're welcome. Glad to help.

Sir Aladin,

I have a follow-up question.

The formula worked but I need to only show single result for each dealer.
I don't want to see a duplicate of each dealer.
In other words,if the same dealer exist for the same location, it will disregard the other.

Is that possible?

 

[Aladin Akyurek]

Sir Aladin,

I have a follow-up question.

The formula worked but I need to only show single result for each dealer.
I don't want to see a duplicate of each dealer.
In other words,if the same dealer exist for the same location, it will disregard the other.

Is that possible?

Does what you describe happen in the exhibit you posted? If not, can you post an example?

 

[markmzz]

Try this:

Layout

DEALER CODE	LOCATION	RESULT
ASMART	ARIAS WHSE	ACAPARROS
PBAKEXMILL	ARIAS WHSE	AMSERCO
ACAPARROS	METRO MANILA	CSC
ASMKTG	ARIAS WHSE	DAETSLK
AMSERCO	PROVINCE	EGSMDSE
ASIATIC	ARIAS WHSE
H-IIXMILL	ARIAS WHSE
CSC	METRO MANILA
DAETSLK	PROVINCE
DAETSLK	PROVINCE
EGSMDSE	PROVINCE
************	**************	*************

<colgroup><col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody> </tbody>

Formula

In C2 - use Enter to enter the formula

=IFERROR(INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(C$1:C1,$A$2:$A$12)+ISERROR(MATCH($B$2:$B$12,{"METRO MANILA","PROVINCE"},0)),),0)),"")

And copy down.

Markmzz

 

[Lifecoachlee]

Thank you everyone. I figured it out already.

Sir Aladin, with this formula, can I add another location aside from metro manila and province?


=IFERROR(INDEX($A$3:$A$13, SMALL(IF(ISNUMBER(MATCH($B$3:$B$13,{"metro manila","province"},0)), ROW($B$3:$B$13)-ROW($B$3)+1),ROWS($C$3:C3))),"")</pre>

 

[Aladin Akyurek]

Thank you everyone. I figured it out already.

Sir Aladin, with this formula, can I add another location aside from metro manila and province?


=IFERROR(INDEX($A$3:$A$13, SMALL(IF(ISNUMBER(MATCH($B$3:$B$13,{"metro manila","province"},0)), ROW($B$3:$B$13)-ROW($B$3)+1),ROWS($C$3:C3))),"")
</PRE>

Yes. Instead of extending the array list in the formula itself, we can use a criteria range, as shown below.

DEALER CODE	LOCATION	METRO MANILA
		PROVINCE
ASMART	ARIAS WHSE	PUERTO
PBAKEXMILL	ARIAS WHSE	RESULT
ACAPARROS	METRO MANILA	ACAPARROS
ASMKTG	ARIAS WHSE	AMSERCO
AMSERCO	PROVINCE	CSC
ASIATIC	ARIAS WHSE	CSC
H-IIXMILL	ARIAS WHSE	DAETSLK
CSC	METRO MANILA	EGSMDSE
CALOSUY	ARIAS WHSE
CSC	PUERTO
DAETSLK	PROVINCE
EGSMDSE	PROVINCE

<COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4238" width=119><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4636" width=130><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4750" width=134><TBODY>
</TBODY>

Define Ivec using Formulas | Name Manager as referring to:

=ROW(Dealers!$A$3:$A$14)-ROW(Dealers!$A$3)+1

Note that this definition is added in order to speed up the processing. Adjust the name of the data sheet Delaers to suit.

As can be seen in the exhibit, C1:C3 houses three criterion locations.

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

=IFERROR(INDEX($A$3:$A$14,
  SMALL(IF(ISNUMBER(MATCH($B$3:$B$14,$C$1:$C$3,0)),
  Ivec),ROWS($C$5:C5))),"")

If there are no dealer code that is the same at different locations, keep the foregoing set up as is.

If the same dealer code occurs at different locations and you don't want them to be listed more than once...

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

=IFERROR(INDEX($A$3:$A$14,SMALL(IF(FREQUENCY(IF($A$3:$A$14<>"",
  IF(ISNUMBER(MATCH($B$3:$B$14,$C$1:$C$3,0)),
  MATCH($A$3:$A$14,$A$3:$A$14,0))),Ivec),Ivec),
  ROWS($C$5:C5))),"")

See the workbook that implements the foregoing set up:

https://dl.dropboxusercontent.com/u/65698317/Lifecoachlee%20dealer%20sublist.xlsx

 

[markmzz]

Another way:

Layout

DEALER CODE	LOCATION	RESULT		Criteria Range
ASMART	ARIAS WHSE	ASMART		METRO MANILA
PBAKEXMILL	ARIAS WHSE	PBAKEXMILL		PROVINCE
ACAPARROS	METRO MANILA	ACAPARROS		ARIAS WHSE
ASMKTG	ARIAS WHSE	ASMKTG
AMSERCO	PROVINCE	AMSERCO
ASIATIC	ARIAS WHSE	ASIATIC
H-IIXMILL	ARIAS WHSE	H-IIXMILL
CSC	METRO MANILA	CSC
DAETSLK	PROVINCE	DAETSLK
DAETSLK	PROVINCE	EGSMDSE
EGSMDSE	PROVINCE
************	**************	*************	**	**************

<tbody>
</tbody>

Formula

In C2 - use Enter to enter the formula

=IFERROR(INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(C$1:C1,$A$2:$A$12)+ISERROR(MATCH($B$2:$B$12,$E$2:$E$4,0)),),0)),"")

And copy down.

Markmzz