Reverse VLOOKUP?

[PerryMBS]

I have a table A1:J10 with item labels in column A and months across in row one. Within the table each cell has a value, either "Received" or "Requested".


I'm looking to create a list with a formula that can look up each cell that is still "Requested" and return the Month and item. For example if item B in Feb and item D in Dec are still "Requested" I'd like to see a list:


Requested:

Item B - Feb

Item D - Dec


It's possible that all items are still "Requested" so if there are say 10 items there is a possibility for 120 items in the list.


Any help is much appreciated. THANKS! New to this site and love it already!

 

[NARAYANK991]

Hi ,


There are possibly three ways to do this :


1. Use a VBA procedure ; this would be the simplest way


2. Use several helper columns ; more cumbersome , but still quite easy.


3. Use only one column ; formulae may be quite lengthy and difficult to understand , leave alone troubleshoot.


Do you have any preference ?


Narayan

 

[srinidhi]

hey Perry the easiest thing you can do is to create a pivot table & filter on the Received you will get all the data which are is in Received status.

If you are using 2007 & 2010 put u r data in a table. As your data increases by the day just refresh the pivot table & u r updated data is ready

 

[PerryMBS]

@NARAYANK991 & @srinidhi thank you so much. I totally spaced and didn't realize that a Pivot Table would probably be perfect for this. I'll give it a try and let you know how it goes. Thanks a ton!!!


-Perry

 

[PerryMBS]

@NARAYANK991 just out of curiosity what would the VBA code look like? I'm not great with VBA but I know my way around well enough to implement the code.


For my purposes the pivot table isn't working quiet correctly. Bummer.

 

[SirJB7]

Hi, PerryMBS!

VBA code should look like this

-----

Sub CreateListRequested()

' constants

Const ksInput = "TableInput"

Const ksOutput = "TableOutput"

Const ksRequested = "Requested"

' declarations

Dim rngI As Range, rngO As Range

Dim I As Long, J As Integer, K As Long

' start

Set rngI = Range(ksInput)

Set rngO = Range(ksOutput)

' process

' output

With rngO

' clear

.ClearContents

' titles

.Cells(1, 1).Value = "Product requested"

.Cells(1, 2).Value = "Request date"

' counter

K = 1

End With

' input

With rngI

For I = 2 To .Columns.Count

For J = 2 To .Rows.Count

If .Cells(I, J).Value = ksRequested Then

K = K + 1

rngO.Cells(K, 1).Value = .Cells(I, 1).Value

rngO.Cells(K, 2).Value = .Cells(1, J).Value

End If

Next J

Next I

End With

' end

End Sub

-----

Assuming you have defined 2 named ranges (TableInput and TableOutput) and that you have formatted column 2 in TableOutput as row 1 in TableInput.

Regards!

 

[SirJB7]

Hi, PerryMBS!

Sorry for losing the indentation, but I still don't know how to copy & paste easily from Excel to the forum area.

Before pressing "Send post" it always looks fine, but not after being sent...

Regards!

 

[SirJB7]

Hi, PerryMBS!

I've just learned (thanks to prasaddn) how to accomplish the indentation.

I'm trying now here. I apologize if I make any mistake.

-----

[pre]

Sub CreateListRequested()
' constants
Const ksInput = "TableInput"
Const ksOutput = "TableOutput"
Const ksRequested = "Requested"
' declarations
Dim rngI As Range, rngO As Range
Dim I As Long, J As Integer, K As Long
' start
Set rngI = Range(ksInput)
Set rngO = Range(ksOutput)
' process
'  output
With rngO
'   clear
.ClearContents
'   titles
.Cells(1, 1).Value = "Product requested"
.Cells(1, 2).Value = "Request date"
'   counter
K = 1
End With
'  input
With rngI
For I = 2 To .Columns.Count
For J = 2 To .Rows.Count
If .Cells(I, J).Value = ksRequested Then
K = K + 1
rngO.Cells(K, 1).Value = .Cells(I, 1).Value
rngO.Cells(K, 2).Value = .Cells(1, J).Value
End If
Next J
Next I
End With
' end
End Sub

[/pre]
-----

It appears to be a simple back tick at begin/end of code.

Let's see what happens...

Regards!

 

[PerryMBS]

@SirJB7 thank you so much for this. Although I don't have time today to test, it will be first thing I do tomorrow morning. I'll check back in and let you know how it goes.


Thanks again!


-Perry

 

[PerryMBS]

Worked like a charm!!!!!!!! Thank you so much @SirJB7 Really really awesome!

 

[SirJB7]

Hi, PerryMBS!

Thanks for your feedback. Welcome back anytime.

Regards!