Returning the Row and Column titles of a certain value

T

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
 
S

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
 
B

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
 
T

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
 
T

Tibbs

swatsp0p said:
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
Click to expand...

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
 
S

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:

Code:
--------------------

Sub cck2()
'find items marked as Incorrect

rr = 10
For st = 2 To 6 ' columns
For prod = 2 To 8 'rows
Cells(prod, st).Select
Select Case ActiveCell.Column
Case "2"
collet = "B"
Case "3"
collet = "C"
Case "4"
collet = "D"
Case "5"
collet = "E"
Case "6"
collet = "F"
End Select
If Cells(prod, st) = "Incorrect" Then
Cells(rr, 1) = ActiveCell.Row & " " & collet
rr = rr + 1
End If
Next prod
Next st
End Sub

--------------------


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
 
R

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
 
T

Tibbs

Ron - that does it... It needs a bit of editing afterwards, but it'
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 5
colums, do I have to extend the Select Case ActiveCell.Column portio
down to 500?

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

Chri
 
B

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?
 
R

Ron Coderre

For a VBA approach, you might try this:


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

Regards,
Ron
 
S

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:

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
 
Top