Index/Match/Offset query

[vaughan198]

Hi,
I am doing a project that I'm struggling with a bit. I wondered if someone could help?
I have one table which contains calculated data that I want to apply categories to another table.


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Industry[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Energy[/TD]
[TD]25[/TD]
[TD]40[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Retail[/TD]
[TD]20[/TD]
[TD]57[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]Chemicals[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]


So on my other table I would like to categorize my data according to the table above.

If the industry matches the industry in column A I would like the score in the other table to be categorized using the criteria above (1,2 or 3).

I am struggling how to get it to do that.

Thanks very much.

 

[Gerald Higgins]

Hi, welcome to the board.

I think I understand roughly what you want to do, but not exactly.

Can you give us maybe 2 or 3 different examples, with what the results should be in each case, and why ?

 

[vaughan198]

So we have the table above on one sheet.
On another sheet we have the main data table with 800 or so rows.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Industry[/TD]
[TD]Score[/TD]
[TD]Lookup Category[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]Energy[/TD]
[TD]42[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Retail[/TD]
[TD]22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TRY[/TD]
[TD]Chemicals[/TD]
[TD]16[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
and what I want to do is use the other table to populate column D from the other table using the score in column in C

 

[Gerald Higgins]

Please refer to post #2 .

Can you give us maybe 2 or 3 different examples, with what the results should be in each case, and why ?

 

[vaughan198]

So if you look at my post number 3.
Cell D2 should display "2". Because Industry is "Energy" and "Score" is "42"
same with the other rows.
Thanks so much.

 

[hamzah]

Hi im new to excel and doing macros in vba, I just need some help in filtering out files by the latest created or modified dates. I basically want to count files in certain directory, which I have done but I want to be able to filter out depending on date created/modified. Hope somebody could help, Thank you in advance much appreciated

The code below just counts the number of files in a certain directory/folder.

Option Compare Text
Option Explicit

Function CountFiles(Directory As String) As Double
'Function purpose: To count all files in a directory
Dim fso As Object, _
objFiles As Object

'Create objects to get a count of files in the directory
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set objFiles = fso.GetFolder(Directory).Files
If Err.Number <> 0 Then
CountFiles = 0
Else
CountFiles = objFiles.Count
End If
On Error GoTo 0
End Function

Sub TestCount()
'To demonstrate the use of the CountFiles function
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.folderexists(Selection) Then
MsgBox "I found " & CountFiles(Selection) & " files in " & Selection, _
vbOKOnly + vbInformation, CountFiles(Selection) & " files found!"
Else
MsgBox "Sorry, but I can't find the folder: " & vbCrLf & Selection _
& vbCrLf & "Please select a cell that has a valid" & vbCrLf & _
"folder name in it!", vbOKOnly + vbCritical, "Error!"
End If

End Sub

 

[FormR]

Cell D2 should display "2". Because Industry is "Energy" and "Score" is "42"

Hi, something like this maybe:

Excel 2013/2016
	A	B	C	D
1	Name	Industry	Score	Lookup Category
2	XYZ	Energy	42	2
3	ABC	Retail	22	1
4	TRY	Chemicals	16	1
Sheet1

Cell Formulas
Range	Formula
D2	=LOOKUP(C2,INDEX(Sheet2!$B$2:$D$4,MATCH(B2,Sheet2!$A$2:$A$4,0),0),Sheet2!$B$1:$D$1)

Excel 2013/2016
	A	B	C	D
1	Industry	1	2	3
2	Energy	25	40	50
3	Retail	20	57	55
4	Chemicals	15	25	50
Sheet2

 

[vaughan198]

Epic! Works like a charm!
Thanks so much all.