simplifying a complex formula and selecting a changing range

[sarlo00]

I have a sheet (call this sheet Sheet2!) that I've had help on in several other posts which looks like this:

Book2
	A	B	C	D	E	F
1	THEABCCOMPANY	BCA	-	ITSCustomPPO	-	0
2		BCC	ITSCustomPPO	ITSCustom	1	5
3		BCD	-	-	-	0
4		BCE	-	-	-	0
5		BCF	-	-	-	0
6		BCG	-	-	-	0
7		BCH	ITSCustom	-	2	0
8		BCJ	-	-	-	0
9		BCK	ITSCustom	-	-	3
10		BCN	ITSCustomPPO	-	-	914
11		BCO	-	-	-	0
12		BCP	ITSCustomPPO	-	-	8
13		BCQ	ITSCustomPPO	-	-	16
14		BCT	-	-	-	0
15		BCV	-	-	-	0
16		BCW	ITSCustomPPO	-	-	30
17		BCY	-	-	-	0
18		BCZ	ITSCustomPPO	-	-	532
Sheet2

 

[Aladin Akyurek]

Re: simplifying a complex formula and selecting a changing r

As response to your last post: I think I posted every formula the exhibit contains in the text of the post.

Would someone help me understanding the use of REPT("z",255)?

It's largest string in lexical ascending order.

LOOKUP(REPT("z",255),A1:A20) returns the contents of the lowest row with text data in it?

Yes.

MATCH(REPT("z",255),A1:A20) returns the row number of the largest text string in Column A?

Or does it return the row number of the last text string in Column A?

It returns the position of the last cell in use (with a text value) in the range, not the row number of the last cell in use. Sometimes the position and the row coincide.

 

[sarlo00]

Re: simplifying a complex formula and selecting a changing r

Aladin,
I was able to replicate your worksheet in a sample worksheet. When I used the formulas in the worksheet that I am working on however, the following formula is giving me problems.

=IF(ISNA(D16),#N/A,MATCH(IF(A16<>"",A16,LOOKUP(REPT("z",255),$A$1:A1)),Custom!A:A))

The "start" column returns the correct value of 16. The "end" column, containing the previous formula, is returning 106. The Custom! sheet which contains similar data as my example Sheet2! has 107 rows worth of data. Since cell D16 is not #N/A, the formula evaluates the MATCH. Since cell A16 is not blank, the MATCH formula simplifies to

MATCH("THE ABC COMPANY",Custom!A:A)

The ABC Company occupies rows 16 through 33 in the Custom! sheet. The formula returns 106 though?????

 

[Aladin Akyurek]

Re: simplifying a complex formula and selecting a changing r

Aladin,
I was able to replicate your worksheet in a sample worksheet. When I used the formulas in the worksheet that I am working on however, the following formula is giving me problems.

=IF(ISNA(D16),#N/A,MATCH(IF(A16<>"",A16,LOOKUP(REPT("z",255),$A$1:A1)),Custom!A:A))

The "start" column returns the correct value of 16. The "end" column, containing the previous formula, is returning 106. The Custom! sheet which contains similar data as my example Sheet2! has 107 rows worth of data. Since cell D16 is not #N/A, the formula evaluates the MATCH. Since cell A16 is not blank, the MATCH formula simplifies to

MATCH("THE ABC COMPANY",Custom!A:A)

The ABC Company occupies rows 16 through 33 in the Custom! sheet. The formula returns 106 though?????

Sarl,

Assuming that the data is sorted on the company column, one possibility is that the company names are misspelled or have additional chars like a space around. Try first to apply ASAP Utilities on the company column. It's freely downloadable from asap-utilities.com.

 

[sarlo00]

Re: simplifying a complex formula and selecting a changing r

The Company Name column in the Custom! sheet was not sorted. After sorting, the formula worked. So, the MATCH formula works similarly to Vlookup in as much as the source data needs to be sorted. Great! I'll add that info to my Excel file ... in my brain. :wink:

Thanks again Aladin!
You amazingly always have the answers. ray: