Data in Corresponding Row

[dfxryanjr]

I want to filter through data based on the value in column A, and then also get the corresponding data in the same row. For instance, if I have three columns (A-C) labeled:
SALARY, NAME, COMPANY

and in D1 type "=max(A:A)", I would also like the corresponding values in column B and C. So if the highest salary is 100,000 and belongs to Jim at Company X, the output would be as follows:
D1= 10,000 E1= Jim F1= Company X

I would like to do this in the simplest way possible (ideally no VBA), just functions.

Im open to any feedback or ideas.

Thanks

[Alf]

You could use the "VLOOKUP" function. If you define range A1 to C? as data you could use a formula in "E1=VLOOKUP(D1,data,2,False)" and in "F1=VLOOKUP(D1,data,3,False)"

Alf

[dfxryanjr]

Hey Alf, thanks a bunch, that does the trick.. on a side note, is there any way to change the coordinates of the data based on an IF function within the VLOOKUP? I have D1 as an IF function that says if the average value of column A is less than 5, take the MAX of column B, and if false, take the max of column A. So I want my VLOOKUP data set to be dynamic as well.

Thanks

[Alf]

Could you perhaps upload a small sample file. It's easier for me to work with a "real" file and there are less chance that I should misunderstand what you wish to achieve.

If the best and easiest solution should involve a macro is that ok with you?

Alf

[dfxryanjr]

To your macro question, I have a very minimal comprehension of VBA coding so a macro solution would be fine if it would be easy enough to implement.

As far as the attached data goes (i have info on both worksheets which is similar to my real life example): the idea is if someone plays a lot of soccer (which is most important in this case), they wont play a lot of football. So the IF function I wrote in E2 finds the max value of either soccer or football (soccer being given the first priority, then football). Seeing as VLOOKUP uses the left most column in the DATA set as the reference column, I need to have a dynamic VLOOKUP so that if E1 takes the soccer max, the soccer column is the leftmost in the data set, but if E1 takes the max of the football column, that becomes the left most in the data set.

I probably didn't convey that too well, but the attached data should be easy enough to comprehend.

Thanks

[Alf]

Sorry about the delay but ”She who must be obeyed” i.e. my wife had some specific ideas of how I should spend my time on Saturday/Sunday.

Not really sure I really understand your request “VLOOKUP data set to be dynamic”
You could always use a formula like

Please Login or Register  to view this content.

where you either have two different defined ranges for your data.

You could also have a formula

Please Login or Register  to view this content.

and change your VLOOKUP value i.e. 2 or 3 to the cell address E1.

Alf

Ps Don't know if this is really what you are looking for. If not don't hesitate to ask for further information.

[newdoverman]

Here is one way of doing it. Enter this formula in E2:

Please Login or Register  to view this content.

and this one in F2:

Please Login or Register  to view this content.

[dfxryanjr]

awesome this is what i needed. Thanks for the help; both alf & newdoverman

[dfxryanjr]

random side question since you seem well-versed in excel & vba... my experience with macros is limited to "recording macros". but with that said i use a lot of functions in the recorded macro in order to summarize large pools of data easily. My next challenge is to use a recorded macro to automatically affect every worksheet. Any thoughts on how i could get this to happen given that I am only using "record macro". I was thinking i will have to insert some type of code into the VBA editor after I stop recording.

[dfxryanjr]

*^Disregard Above^*

That is a poor, more complex, and inaccurate description of what I need. I need a macro that goes through a folder on my computer (every file within this folder is an excel sheet formatted the exact same way). The macro needs to open each file and sum column C, sum column D.. through column E, and list all of the results in 1 document, in corresponding column form.

Each excel file within this folder also had multiple tabs, so i need the macro to sum column C-E in this new document, do the same for each worksheet, then repeat for each file within this folder.

That might have not made sense , so attached is an example file.

I know this is a large request, but any input on this would be huge..
Thanks

[newdoverman]

Sorry, can't help you with the VBA as I only record macros like you do.

[Alf]

Perhaps something like this could be a starting point?

To test first change line

Please Login or Register  to view this content.

to the drive and the folder where you xlsb files are stored. Then run macro "Extrac_data". Macro will open all xlsb files in specified folder loop through all the sheets in every file and copy the sum value from column C, D and E to file "Master_Imp".

It will also write file in column A and sheet name in column B.

Alf

[dfxryanjr]

Alf;
what you described is exactly what I need. And taking the file name and sheet name is incredible as I would have needed to do that regardless, so thanks for taking the time to help me out with this.
There's a slight problem with how this macro runs however.. I changed the drive and folder path, ran it, and the only output I got was in Row 1, with the headers (file name, sheet name, etc).
In trying to do a layman's troubleshoot, I changed the line below the sPath line (the sFile line) to .xls instead of .xlsm seeing as all the files in the folder are .xls

After doing this I re-ran the macro and received the same result.

But like I said the intended result is amazing, so thanks immensely.. any help troubleshooting this to get it to fully work will be amazing too

[Alf]

Using your uploaded file “Schools.xlsb” as the raw model I changed the file name and made 3 copies of it. Running the macro my result looked like this (Macro_result.png).

As you say you only got row 1 as output it seems to me that the sPath is not right or that the layout of the xls files differ from the xlsb layout.

Could you upload a “real” xls file? I would also like to see string sPath.

to .xls instead of .xlsm

Must be a typo as sFile was “*.xlsb” not “*.xlsm” in macro.

Alf

[dfxryanjr]

Yes that was a typo, but regardless, I changed .xlsb to .xls to no avail.

Attached is a real example..

[Alf]

As I had no problem importing your ast uploaded file (see Macro_for_xls.png) I suggest that you record a macro where you go through the stages of importing a range from one of your xls files to the "Master_Imp.xlsm" file.

Open up file "Master_Imp.xlsm" and then click "Record macro" and import a range from one of the xls files, close the xls file and click "Stop recording"

Excamine the new macro probably called Macro1. There you will see the string to the folder where the xls files are stored.

In my case the macro starts like this:

Please Login or Register  to view this content.

so my sPath is then

Please Login or Register  to view this content.

The last "\" is most important.

You could also change the sFile string to

Please Login or Register  to view this content.

The downside of this change is that macro will open xls, xlsx, xlsm and xlsb files.

Alf

[dfxryanjr]

it worked!! thanks so much

the problem was the "\" which I didn't originally have inserted.

I appreciate all of the consistent help Alf

[Alf]

Glad to be of help and thank for feedback.

If you liked my answer click on small star bottom left (Add Reputation) in my post and give a rating to my answer.

As this seems to solve your problem could you also please mark your thread "Solved" as per forum rules.

Alf

Ps

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

[Alf]

Thanks for the rep.

Looking at the result I'm not happy as I find the layout a bit to "cluttered". Instead of having it looking like this (Result1.png) it could be presented like this (Result2.png).

If you also prefer the second layout just add the following lines to the macro:

Please Login or Register  to view this content.

The lines should be added after the line

Please Login or Register  to view this content.

Alf

[dfxryanjr]

You're right that it the new way looks a lot less cluttered, so thanks for taking the initiative to write up a solution.. for my purposes the output in "Results1" is more ideal so that once I sort the data I can still easily tell what file each thing came from, although I did try the new way too.
Another question in regards to this: if I want to make a new column that sums up the last 30 days of activity (from the original data sheets) for C-E, how would I go about doing that? The thing I dont know how to do is tell the computer to go to the last cell with contents in it, and sum that and the 29 cells above it.

[Alf]

I dont know how to do is tell the computer to go to the last cell with contents in it, and sum that and the 29 cells above it.

To find the last row (cell value) in a column you could use code like this:

Please Login or Register  to view this content.

This command will go from the bottom and upwards till if finds the first value in the C column and since ".Row" is the last part this will give you the row number.

If last value in column C is in cell C43 the command above will go "upwards" till it find the first value and give you the row number 43.

You would also need some kind of error trapping since you wish to sum the last 30 days so you test if this is so:

Please Login or Register  to view this content.

Why I'm using 31 for testing is that I assume the first row contain headings. You could also use

Please Login or Register  to view this content.

To find the range you need top row and simple arimetic should do the trick

Please Login or Register  to view this content.

Alf

[dfxryanjr]

this seems like this would affect the "Master_Imp" file rather than the source file.. also, when you say *insert code here*, do I just say "=sum"? if not, how do I tell it to sum the range specified in the above code?

[Alf]

this seems like this would affect the "Master_Imp" file rather than the source file

Yes think of "Master_Imp" as your trained seal who can do certain tricks. If you wish it to perform a new trick it must be taught to do so i.e. code must be rewritten. On the other hand you don't have to encourage it with a fish every time you wish it to perform.

As this code is written for looping through all files in a folder and extracting data the basic functions are still valid. What has change is that instead of summing a column you just wish to get the sum for the last 30 days.

First you need to change the line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

as I'm adding an extra integer in order to sum the last 30 days.
Then I wish to test if the column to sum contains at least values for 30 days or more and if so change my sum formula.

Replace these lines

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

The code now checks if j is equal to or greater than 31 (As first row contains headings data goes from row 2 to ???)
If this condition is fulfilled the sum formula is rewritten to sum just the last 30 days. If not it sums just available data in column.

I'm not to happy about that as you don't know if your result is based on the last 30 days or less data than 30 days. It is possible to add extra information in "Master_Imp.xlsm" of how may rows with data was found in active sheet or that sheet could be skipped. You have a think about this problem and how you would like it solved.

On a previous posting you had this question

if I want to make a new column that sums up the last 30 days of activity

Not sure if I've answered that or have I?

Alf

[dfxryanjr]

You make a good point that the output wouldn't be easily deciphered as you can't tell if the information is the last 30 days, or less.. So what if, instead of using the if statement to sum the last 30 days if there are at least 31 rows of data, we keep it as before, and then have it sum the last 30 days of data in a new column.. So on Master_Imp;
Column A is file name
Column B is worksheet name
Column C is sum of all data in column C of the source file
Column D is sum of all data in column D of the source file
Column E is sum of all data in column E of the source file
_______________________________________________
Then add three more columns that sum the last 30 days of the perspective source info. So;
Column F is sum of last 30 days of column C
Column G is sum of last 30 days of column D
Column H is sum of last 30 days of column E

[Alf]

Here comes a revised code for your last requests. Replace old code with the new one. Don't forget to change the sPath

Please Login or Register  to view this content.

to the one you use.

Please Login or Register  to view this content.

I've tested it and as far as I can see it does what you wish it to do. Still I would recommend you also do a bit of testing. I found a minor "glitch" in the example file column F is hidden. Will not affect the result but as I stepped through my macro I was first a bit puzzled as I could not find all results then realised that this was caused by a hidden column (F).

Alf

[dfxryanjr]

Wow this is great.. I haven't encountered any bugs (not even a hidden F column)!
I've also gone ahead and copied your "If j >+ 31 then..." code, pasted it below, and changed the values so that I can have a 1 week, 2 week, and 7 week average. (After the sum function, I divided it by 7, 14, 49 to get the average). This is great for being able to use this output to diagnose high & low points..

I know I've said that "this is all i need" before, however, I really am almost fully there.

There are a couple quirks in the data that I've tried to figure out how to manage on my own, however, it is proving to be a headache.

First off, in the "wellness data sheet", there is a worksheet with a graph summary of every worksheet that contains data. On the "Master_Imp" output, this is displayed as rows of blank cells in the C-H columns, with column A showing the file name, and column B showing the worksheet name (the ones with the graph have the same name with the addition of "Graph" at the end).

I've been trying to find a way to delete any row in which a cell in column B contains the word "Graph" (A wildcard will need to be added as the true output will be "School1Graph")

I have found this code:

Sub Delete_Rows()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A1:C20"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "Apple" _
Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete
End Sub

But I have been having trouble trying to place it into the existing code (I tried removing or moving certain parts of this code, etc)

________________________________________________________
^^^ that is the main thing that needs to change.
Additionally, I would LIKE if conditional formatting can be applied to an entire row (I will describe below), however, this is not the top priority and I have not spent sufficient time trying to figure out how to do it on my own....

With that said, this would be the desired conditional format output

if(1 week average <= 85% of the 2 week average OR <= 75% of the 7 week average, make that row red)

Alf you're the man for all the help you've given me, and I hate to keep asking for "just 1 more thing", however, I believe this would truly be it.. until tomorrow when theres just 1 more thing again.. Just kidding. I do really think this is it, and I you have truly been a huge help to me. Any final thoughts on this would go a long way.

Thanks

[Alf]

there is a worksheet with a graph summary of every worksheet that contains data

As I see it there are two possible solutions. Since there seems to be a naming convention i.e. "Graph" at the end of the sheet name one could test for that and ignore those sheets that contains "Graph".

Or loop through all sheets and remove graph values from "Master_Imp.xlsm"

My personal preference is to skip the sheets where the string "Graph" is found but I'm using a function called "InStr" and "InStr" requires a perfect match i.e. if I search for "Graph" "InStr" will not "find" "graph".

In macro sheet name is checked if it contains string "Graph". If found value is greater than zero i.e. value is starting position of string "Graph" then macro jumps to line "skipper:" the line above "Next".

Please Login or Register  to view this content.

As for the conditional format I need some example of how it looks so I can do a bit of testing as I'm more a macro kind of person than a formula guy.

Alf

Ps When posting code don't forget to wrap it or moderators gets upset!!

[dfxryanjr]

what do you mean wrap it?

[Alf]

See # icon and if you hover with mose pointer over it it says "Wrap [Code] tags around selected text"

So before you write code in a posting click on the # icon and get [Code][Code] then you write your code between these [Code]

Alf