Returning the Row and Column titles of a certain value

[Tibbs]

I have a sheet, with a list of products across the top and a list of stores down the side.

Against the rows and columns there are two possible data values, Correct and Incorrect.

What I am trying to do is to return a list of all the Store and Product combinations that cross reference with Incorrect.

My experience of array formulae is vitually zero, and I have a feeling that something clever with MATCH may well do the trick.

Can anybody help?

Many thanks,

Chris

[swatsp0p]

You don't really say what kind of 'list' you are looking for, but if you want to have a grid layout with the store listed below the Product that is "Incorrect", use this formula format: (where column A is your list of stores, beginning in row 2, and your products are listed in B1:nn1.)

In another area of your sheet (or a new sheet) build a matching grid to this data grid and enter this formula in B2 and copy down and over as needed to fill the grid:

=IF(ISERROR(MATCH("Incorrect",B2,0)),"",$A2)

The result will be the name of the store (from col. A) in each cell beneath the Product name that holds an 'Incorrect' value.

Does this work for you?

Bruce

[bj]

I think a macro would do this best something like
if the product list were in B1:AA1
and the store list were in A2:A100

if the data sheet is clear below 110

sub cck()
rr=110
for st = 2 to 100
for prod = 2 to 27
if cell(prod,st)="incorrect" then
cells(rr,1)=cells(st,1)
cells(rr,2)=cells(1,prod)
rr = rr+1
end if
next prod
next st
end sub


"Tibbs" wrote:

>
> I have a sheet, with a list of products across the top and a list of
> stores down the side.
>
> Against the rows and columns there are two possible data values,
> Correct and Incorrect.
>
> What I am trying to do is to return a list of all the Store and Product
> combinations that cross reference with Incorrect.
>
> My experience of array formulae is vitually zero, and I have a feeling
> that something clever with MATCH may well do the trick.
>
> Can anybody help?
>
> Many thanks,
>
> Chris
>
>
> --
> Tibbs
> ------------------------------------------------------------------------
> Tibbs's Profile: http://www.excelforum.com/member.php...o&userid=15947
> View this thread: http://www.excelforum.com/showthread...hreadid=380847
>
>

[Tibbs]

BJ,

Looks like it will do what I want, but it comes up with an error:-

Compile Error:

Sub or Function not defined

And it highlights Cell on the line :

If cell(prod, st) = "incorrect" Then

Cheers,

Chris

[Tibbs]

You don't really say what kind of 'list' you are looking for, but if you want to have a grid layout with the store listed below the Product that is "Incorrect", use this formula format: (where column A is your list of stores, beginning in row 2, and your products are listed in B1:nn1.)

In another area of your sheet (or a new sheet) build a matching grid to this data grid and enter this formula in B2 and copy down and over as needed to fill the grid:

=IF(ISERROR(MATCH("Incorrect",B2,0)),"",$A2)

The result will be the name of the store (from col. A) in each cell beneath the Product name that holds an 'Incorrect' value.

Does this work for you?

Bruce

Bruce,

I tried this and it doesn't seem to work.

What I'm attempting to do is to do is to report all the combinations of store and product that combine to return the value of incorrect

eg. Where Product is A - D and Store is 1 - 4

xAxxxxxxBxxxxxxxxCxxxxxD
1 Correct Incorrect Correct Correct
2 Incorrect Correct Incorrect Correct
3 Correct Incorrect Correct Correct
4 Correct Correct Correct Incorrect

I'd like to run a formula that would return:-

1 B
2 A
2 C
3 B
4 D

I think I'd need an array formula, but I have no idea how these work!

Thanks,

Chris

[swatsp0p]

I think bj is correct. A macro is your best bet. I have modified bj's fine work to maybe better meet your requirement of output in the form of Row number:Column Letter. (the error message was a typo: Cell(prod, st).Select needs to be: Cells(prod, st).Select) This is tested and works:

Paste this in a worksheet module:

Please Login or Register  to view this content.

This assumes your data begins in B2 and flows to F8. Adjust as needed the st and prod numbers to reflect the actual number of rows and columns. Output will begin in row 10 (you can edit this in the rr = 10 statement) and be in the format of "2 B" [no quotes]. "Incorrect" is case sensitive. "incorrect" will not be identified.

Good Luck.

Bruce

[bj]

senior moment
Try Cells instead of cell

"Tibbs" wrote:

>
> BJ,
>
> Looks like it will do what I want, but it comes up with an error:-
>
> Compile Error:
>
> Sub or Function not defined
>
> And it highlights Cell on the line :
>
> If cell(prod, st) = "incorrect" Then
>
> Cheers,
>
> Chris
>
>
> --
> Tibbs
> ------------------------------------------------------------------------
> Tibbs's Profile: http://www.excelforum.com/member.php...o&userid=15947
> View this thread: http://www.excelforum.com/showthread...hreadid=380847
>
>

[Ron Coderre]

I think I found an approach that only uses regular functions:

Sample Data (Cells A1:E5)
Store___ProdA_____ProdB_____ProdC_____ProdD
1______Correct____Incorrect___Correct____Correct
2______Incorrect___Correct____Incorrect___Correct
3______Correct____Incorrect___Correct____Correct
4______Correct____Correct____Correct____Correct

A7: Store
B7: Num Incorrect
C7: Num Remaining
D7: Prod

Enter each of these formulas, when done...copy down
A8: =MAX(A7,1)+(C7=0)

B8: =IF(A7<>A8,COUNTIF(OFFSET($A$1:$E$1,MATCH(A8,$A$1:$A$5,0)-1,),"Incorrect"),C7-1)

C8: =B8-1

D8: =IF(B10>0,INDEX($A$1:$E$1,,-SUMPRODUCT(SMALL(500-((OFFSET($A$1:$E$1,MATCH(A10,$A$1:$A$5,0)-1,)="Incorrect")*(COLUMN(OFFSET($A$1:$E$1,MATCH(A10,$A$1:$A$5,0)-1,)))),B10))+500),"n/a")

My end result was:
Store__Incorrect__NumRem__Prod
1______1_______0________ProdB
2______2_______1________ProdA
2______1_______0________ProdC
3______1_______0________ProdB
4______0_______-1________n/a

Does that do what you want?

Ron

[Tibbs]

Ron - that does it... It needs a bit of editing afterwards, but it's fantastic!

I'm intrigued, however, by the VBA solutions -

BJ - with the correction it works for about 3 cells and then says:

Runtime error '1004':

Application-defined or object-defined error

and then it highlights

Cells(rr, 2) = Cells(1, prod)

Bruce,

Your solution works very well, but my main data is 500 rows and 59 colums, do I have to extend the Select Case ActiveCell.Column portion down to 500?

Thank you all for the time taken on this - I really need to badger work to send me on an Advanged Formula/Array Formula/VBA for Excel course...

Chris

[Ron Coderre]

For a VBA approach, you might try this:

[vba]
Sub TrackStoreIncorrects()
'find items marked as Incorrect
Dim intStoreRowRef As Integer
Dim intProdColRef As Integer
Dim rngGridStart As Range
Dim intRptCtr As Integer

Set rngGridStart = ActiveSheet.Range("L1")
intRptCtr = 0

For intStoreRowRef = 2 To 40
For intProdColRef = 2 To 5
With Cells(intStoreRowRef, intProdColRef)
If UCase(.Value) = "INCORRECT" Then
intRptCtr = intRptCtr + 1
With rngGridStart
.Offset(RowOffset:=intRptCtr, ColumnOffset:=0).Value = Cells(intStoreRowRef, 1).Value
.Offset(RowOffset:=intRptCtr, ColumnOffset:=1).Value = Cells(1, intProdColRef).Value
End With
End If
End With
Next intProdColRef
Next intStoreRowRef

End Sub
[/vba]
Regards,
Ron

[bj]

I had more than one senior moment when I put in that line
check the order correction


sub cck()
rr=110
for st = 2 to 100
for prod = 2 to 27
if cells(st,prod,)="incorrect" then
cells(rr,1)=cells(st,1)
cells(rr,2)=cells(1,prod)
rr = rr+1
end if
next prod
next st
end sub
I don't know why you would get that particular error message
in Debug, what do you see when you put the cursor over the different
variables?

"Tibbs" wrote:

>
> Ron - that does it... It needs a bit of editing afterwards, but it's
> fantastic!
>
> I'm intrigued, however, by the VBA solutions -
>
> BJ - with the correction it works for about 3 cells and then says:
>
> Runtime error '1004':
>
> Application-defined or object-defined error
>
> and then it highlights
>
> Cells(rr, 2) = Cells(1, prod)
>
> Bruce,
>
> Your solution works very well, but my main data is 500 rows and 59
> colums, do I have to extend the Select Case ActiveCell.Column portion
> down to 500?
>
> Thank you all for the time taken on this - I really need to badger work
> to send me on an Advanged Formula/Array Formula/VBA for Excel course...
>
> Chris
>
>
> --
> Tibbs
> ------------------------------------------------------------------------
> Tibbs's Profile: http://www.excelforum.com/member.php...o&userid=15947
> View this thread: http://www.excelforum.com/showthread...hreadid=380847
>
>

[swatsp0p]

I didn't test Ron's code to see how that works, but I trust his solution will do the trick as well.

As for your question regarding my code, yes you would need to adjust the following:
[vba]
rr = 505 ' start list in row 505
For st = 2 To 59 ' columns
For prod = 2 To 500 'rows
[/vba]
as well as setting 59 cases (for each column)

That is pretty tedious. I believe Ron's code does this much simpler.

Thanks Ron and bj.

Good Luck, Chris.

Bruce

[bj]

I agree I like his solution.

The reason you had the problem with my original suggestion was that wtih the
rows and columns reversed in the one line for cells, it was trying to go
outside the spresdsheet. with 500 units.

"swatsp0p" wrote:

>
> I didn't test Ron's code to see how that works, but I trust his solution
> will do the trick as well.
>
> As for your question regarding my code, yes you would need to adjust
> the following:
>
> rr = 505 ' start list in row 505
> For st = 2 To 59 ' columns
> For prod = 2 To 500 'rows
>
> as well as setting 59 cases (for each column)
>
> That is pretty tedious. I believe Ron's code does this much simpler.
>
> Thanks Ron and bj.
>
> Good Luck, Chris.
>
> Bruce
>
>
> --
> swatsp0p
>
>
> ------------------------------------------------------------------------
> swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
> View this thread: http://www.excelforum.com/showthread...hreadid=380847
>
>