Macro and Vlookups to retrieve data via rows

[Ray789]

Hi,

I have some sample data attached which has list of people and the Cars they own.

I want to take this data as the source and list each Car against their owner (can own multiple cars) consequently i.e. in cell B2,B3,B4... I want this in these cells so I don't have to look across a big spreadsheet to find the data. Output required is shown as Output (1).

I want another Output shown as Output(2) showing the status of the cars i.e. Available, In repair by using the source and checking against a table provided.

I have tried using Lookups however I am not getting the output I want so I'm wondering do I need a Macro and a lookup? Help please

[foxguy]

Ray789;

Here's a file that does what you have asked for. Copy of Sample 2 - Lookups.xls
I'm guessing that this is not your actual file. So here's the order that you should put them into your workbook.

L14:Q23
F14:J23
F29:I36
B29:D36

F16:J23 are in different columns than where you had the same data, because F29:I36 refers to them, and it was easier to have the columns line up.

If you can't figure out how the formulas work, let me know.

[Ray789]

Hi Fox,

Awesome work to get this working however because I'm not a Excel Guru at all, I am finding it a challenge to mimic what you have done for my big spreadsheet without doing a number of manual tasks. I thought there maybe a way of running a one or two different lookups which use a macro in the bacground to get Output 1 and 2 out?

Just want to avoid having to do the manual task as the columns may change from time to time.

Thanks for your help though.

[foxguy]

Hi Fox,

Awesome work to get this working however because I'm not a Excel Guru at all, I am finding it a challenge to mimic what you have done for my big spreadsheet without doing a number of manual tasks. I thought there maybe a way of running a one or two different lookups which use a macro in the bacground to get Output 1 and 2 out?

Which do you want?
Formulas like I have?
Or a macro that you have to initiate? It can be set up to trigger when you change any cell, so you don't have to do anything extra to initiate it.

Pros & Cons
Formulas are quicker and cannot be short circuited accidentally without crashing Excel. Formulas are usually easier to debug, because you see all the intermediate steps.

Macros cannot run in the background. Depending on how much data there is, it could stop you working while it finishes it's work (probably not though - I can't imagine that you have that much data, and there is nothing time consuming about what you asked). You cannot always see the intermediate steps, and if an error occurs you may not know it. Also macros have to use Range Names (I will NOT EVER hard code an address into a macro). If you are not comfortable with Range Names, you would have to learn them (probably pretty easy, and definitely a good idea any way). Also, a macro can be turned off by knowledgeable people.

My personal preference is to use formulas unless they are so complicated as to cause me extra work trying to debug them.

Either way, I'll help.
If you want to learn how the formulas work, so you can do some yourself next time you need something, I'll be glad to walk you through them one at a time.
Or if you want a macro, I'd be happy to write it.

FYI, it would be easier and faster for me to write a macro (and obviously less time for you to get it up and running), but I believe you would be better off learning how the formulas work.

[Ray789]

Macro to initiate would be better so I can use this straight away. In parallel I will have a look at Range names. Please let me know where in the macro I need to change the range so it knows where to look for the source data as this will change from spreadsheet to spreadsheet.

Will mark this complete after you have done that for me. Thankyou.

[foxguy]

Ray789;

I presume that "Output(1)" and "Output(2) are not specified in your workbook.
I presume that the worksheet is empty below the last name in "Source".
I am putting Output(1) right below "Source", and Output(2) right below Output(1).
If that's not correct let me know as soon as possible.

[foxguy]

Ray789;

Here's the file Copy of Sample 2 - Lookups.xls
Run the macro "Count_Cars" in this workbook to make sure it's doing what you want.
In the Macro "Count_Cars" look for this at the top:

Please Login or Register  to view this content.

Play around with the addresses for rOutPut1 & rOutput2 so you can see how they work

When you're sure that it's working the way you want then:
Copy Module1 & Module2 into your workbook.
It should be easy enough to replace everything in quotes with the appropriate name & addresses from your workbook.

[Ray789]

Hi Foxguy,

I have been playing with the addresses and they are working fine which is brilliant.The only thing I want now is for the source to be on one sheet (Sheet1) and the Output to be on a seperate page (Sheet2).I got this working with the following code:

Please Login or Register  to view this content.

The only issue I have is I want the rOutput2 to be displayed on Sheet3 at cell A2. I also want the rConditions to be retrieved from another Sheet i.e. Sheet 4. If all this can be controlled from within the above variables that would be great.

[foxguy]

Ray789;

You need to put code tags around your code. I get in trouble with the moderators if I respond to a message without code tags.

You can put code tags around code by:
1) Click "Edit" on the message
2) Click "Go Advanced"
3) Highlight the section of you message that is code and click the # sign above the message.

[foxguy]

Ray789;

Here's the new file Copy of Sample 2 - Lookups Ver 2.xls

There are a few things about it that I assumed. If these are wrong then let me know.
1) Output1 and Output2 are on different sheets.
2) Row 1 of Output2 is headings that you have in rConditions. If you add more possible conditions, it will automatically expand to accommodate them. Also, if there are no cars "In Repair", then the "In Repair" column will be removed from Output2 (same for "Available", "On Lease", etc). If you want the column to remain even if there are no cars in that condition, it will have to be modified.
3) You said you wanted Output2 to be in "A2". I assumed that A1 was for headings. Output2 has the headings in it, so I put it in A1. If A1 is for something else and you do not want it erased, then it has to be modified.

[Ray789]

It all seems to work fine however I need some final tweaks:

Output2 is displayed by numbers which is good however the next step would be to understand which Car is in for Repair/On lease...by owner. I.e. translating the numbers into the Cars. Would be good if this could be display in a new sheet i.e. Sheet5 similar to Output2.

Also Cars which do not fall under a condition i.e. Repair/On Lease.. etc do not appear on Output2.Would be good if they were added to Output2 under a column Unknown. They could be then reviewed and assigned a condition.

Lastly I have a few columns for the Owner i.e. Department, Address, Contact details etc....I want the choice of exporting these to Output1 and Output2. At the moment the Macro only copies the Owner Name. I wasn't quite sure what to change to include further columns for Owner details.

You are a star mate!

[foxguy]

Output2 is displayed by numbers which is good however the next step would be to understand which Car is in for Repair/On lease...by owner. I.e. translating the numbers into the Cars. Would be good if this could be display in a new sheet i.e. Sheet5 similar to Output2.

I can't figure out what you want here. Are you saying that you want to know how many people are using each car. "Nissan Micra would be 3" and "Audi A8 would be 0"?

Also Cars which do not fall under a condition i.e. Repair/On Lease.. etc do not appear on Output2.Would be good if they were added to Output2 under a column Unknown. They could be then reviewed and assigned a condition.

I put this into this file Copy of Sample 2 - Lookups Ver 3.xls
I moved "Fiat Uno" away from the rest on sheet4 so that the macro wouldn't find it (so there is one car that is not listed in "Conditions"

Lastly I have a few columns for the Owner i.e. Department, Address, Contact details etc....I want the choice of exporting these to Output1 and Output2. At the moment the Macro only copies the Owner Name. I wasn't quite sure what to change to include further columns for Owner details.

When you say "Option", does that mean that you want to sometimes include the extra columns and sometimes not? I would have to work on that a while. How would you want to choose whether or not to include the extra columns? A button on the worksheet, a button in the menu, a checkbox on the worksheet, or ???
If you always want the extra columns included, that's fairly easy. I would just have to know how many extra columns to include.

[Ray789]

In regards to the first query I should have been more clear. I have added what I expect the output to be in Sheet5.

Cars that do not fall under a condition seem to be working perfectly fine which is great.

In regards to the extra columns. I have data(text) in columns A-Z and at the moment I am not sure which columns I would need in the outputs. This may vary from time to time. A button in the menu maybe better. If it is on the worksheet it may get deleted when data is being sorted/deleted. I am assuming the button when pressed would ask which columns of data is required i.e. Column A,B,F,H.

In regards to the Outputs (rOutput1,rOutput2,rOutput3). I may only want certain Outputs sometimes so I tried deleting rOutput2 from the sheets and variables section, however I kept getting an error when running the Macro. Can this somehow be controlled. I don't mind deleting a row of code when I need to do this.

[foxguy]

Hi Ray;

In regards to the extra columns. I have data(text) in columns A-Z and at the moment I am not sure which columns I would need in the outputs. This may vary from time to time. A button in the menu maybe better. If it is on the worksheet it may get deleted when data is being sorted/deleted. I am assuming the button when pressed would ask which columns of data is required i.e. Column A,B,F,H.

This doesn't sound hard. The hardest part will be asking the user which columns are wanted in the output.

In regards to the Outputs (rOutput1,rOutput2,rOutput3). I may only want certain Outputs sometimes so I tried deleting rOutput2 from the sheets and variables section, however I kept getting an error when running the Macro. Can this somehow be controlled. I don't mind deleting a row of code when I need to do this.

As part of the process of asking user what columns (s)he wants, I would also ask what outputs are wanted.
I think the best way to handle this would be to have a hidden sheet that is unhidden when the menu button is clicked (but I reserve the right to change my mind). That way it would also save the last request which could be handy in future requests. It could even be easy to save request parameters by name so that they can be called up later.
At the moment the only question I have is; if you only want Output1, do you want the old Output2 and Output3 deleted? I imagine that they should be deleted so that there is no chance of confusion. If you don't want them deleted then I would definitely put a date into them, so that Output1 might be on June 30, 2010 and Output2 might be on July 3, 2010.

I also have one big issue at the moment. For some reason, my computer is acting weird, and I can't always upload files. If I can't upload a file when it's ready, I may have to post it all in a message and you will have to cut & paste it into your workbook from the web site instead of a workbook where you can test without worrying about messing things up.

[Ray789]

Asking the user which columns are needed in the output is fine as long as you can save the last request as you stated. Otherwise it means selecting the columns each time you run the Macro which would be quite time consuming since there will be quite a number of columns.

In regards to your question. Yes if Output1 was selected then the other two Outputs should be deleted to avoid confusion.

You can try uploading the code. If the code is quite big you could try sending me it as a private message so it doesn't clutter this posting. As you wish really.

[foxguy]

Hi Ray;

Here's a preliminary file Copy of Sample 2 - Lookups Ver 4.xls.

Go to Output Form sheet and just verify that this format will work for you.

There is a macro "Create_Output_Form" that creates that sheet. If you add columns to Sheets("Raw Data"), then activate sheets("Output Form"), it will automatically recognize that you have changed columns and regenerate the sheet. It's not doing it automatically yet, but you can check how it works, by adding a column (or removing a column) to sheets("Raw Data") then run "Create_Output_Form" and you will see the column headings adjusted.

The Run Reports button just does what you already have. It doesn't have Output 3 yet.

I'm just checking to see if the format of Output Form sheet will work for you.

I'm planning on having Run Reports check all the CheckBoxes. If any are checked in an Output column, it will run that report.

[Ray789]

Hi Foxguy

A few points:


1 - I tried selecting Name, Department, BMW320 and Run Reports however the same data as before
was showing in Output1 and Output2 i.e.Department was not showing. I am guessing this was intentional because you haven't finished the code.

2 - Having 3 buttons to select all boxes in Output1, Output2, Output3 would be good as you have stated.

The Raw Data and Output Form are working well though with inserting and deleting columns. Looking good.

[foxguy]

1 - I tried selecting Name, Department, BMW320 and Run Reports however the same data as before
was showing in Output1 and Output2 i.e.Department was not showing. I am guessing this was intentional because you haven't finished the code.

Sorry, I wasn't clear. I haven't worked on the reports yet. I just wanted to see if the format for the screen was adequate. If it is not, it might affect how the macro that develops the reports works.

2 - Having 3 buttons to select all boxes in Output1, Output2, Output3 would be good as you have stated.

I don't understand what you're saying here. Are saying that you want a button to select all boxes for a report? Or are you saying you want 3 buttons, one to run each report? Or maybe both?
My original idea was to just have one button. The macro would look for checked boxes and only run those reports where it found at least one checked box.

I now think it would be a good idea to have a way of checking all boxes at once. I would probably do it like I see other programs do it (even Excel Forum), have a checkbox at the top of the column that checks or unchecks the entire column.

If you want 3 buttons (1 for each report), then I would hesitate to have the old reports deleted. I would just make sure there is a date somewhere on the report indicating when it was run.
Makes no difference to me, no extra work either way. So how do you want it - 1 button and delete old reports, or 3 buttons with dates on each report, or what?

BTW, the number of reports run does not affect how fast they run. All reports are calculated at the same time. It would actually take 3 times as long to have the user click 3 buttons. All 3 Buttons would still run the same macro. The only difference is what report(s) to output to a spreadsheet, they are all in memory at that point.
It does take time to notify the user that old info is about to be deleted. Do you want that to remain?

Two final notes:
I think I found a way to get my computer to let me upload the file to Excel Forum. I have to log off (not just close my browser) and restart my browser, and then it lets me upload at least one file before conking out (at least so far it has).
I just started some new medication that is slowing me way down. I only have to take it for a week for a temporary problem with my eye, but it will hold up the final product a bit.

[foxguy]

Hi Ray;

I ran into a problem. I need a decision on your part.
In the Raw Data you want to be able to add columns like Dept, etc. The macro needs to know what columns are part of descriptive data and what columns are part of data to be counted. IOW, how will the macro know that "BMW 320" is not a dept name rather than the name of a car?

The 3 easiest ways to solve this are:
1) leave an empty column between descriptive columns and data. This lets the macro find the correct #. The column can even be a hidden column. This is the way my own spreadsheets work. I put a column with a width of .25 and black cell color.
2) hard code the # into the macro (not recommended). Obviously you would have to go in and change it when you added or removed columns.
3) hard code the contents of the 1st data column into the macro (not recommended). Obviously you would have to go in and change it when you changed the 1st data column's heading.

Let me know which way you want to go, or if you have another solution.

[foxguy]

Hi Ray;

Just realized I had another problem. Can I assume that "Name" will always be included in the reports and will always be column # 1 in the reports?

[Ray789]

Hi Foxguy,

In response to the first post regarding the buttons:

1 button would probably be easier to avoid making the
spreadsheet more complicated than it should be.

In regards to the Raw data query.

I can have a column between the descriptive data and the columns which
need counting i.e. I will call it 'Cars' for now which I would like changing
when needed which does not have to be hidden. On the left to this column will be the descriptive data and towardsthe right columns will be the data to be counted.

In essence going towards option 1.


In regards to the last query:

I guess the purpose for having the Output Form was so you could select whichever column you wanted. So I would like the idea being able to select 'Name' too. This would not always be in column1 in the RawData, it could change.


Let me know if you need any further clarification.

[foxguy]

Hi Ray;

I can have a column between the descriptive data and the columns which
need counting i.e. I will call it 'Cars' for now which I would like changing
when needed which does not have to be hidden. On the left to this column will be the descriptive data and towardsthe right columns will be the data to be counted.

Ok, I will assume that in row 1 of the raw data, there will be a column with the word "Cars" in it. The macro will assume everything to the left is just copied into the report, and everything to the right is counted. The "Cars" column itself will be ignored.

I guess the purpose for having the Output Form was so you could select whichever column you wanted. So I would like the idea being able to select 'Name' too. This would not always be in column1 in the RawData, it could change.

I have made the assumption that the order of the columns in the reports are the same as in Raw Data, just that the reports might not have all the columns. If this is not correct, I need to know. I will have to rethink how to do things.
For example, will the Raw Data have columns in order: Name, Dept, Address, but the report may want the order: Dept, Address, Name?

One last thing, all the counting has been based on row # in Raw Data. As long as each row represents 1 Name, there is no problem, but if you do not have Name in the reports, the data may not make sense. IOW if 4 people are in the same Dept and Name is not on the report, then there will be 4 lines with the same Dept but no way to tell the difference between the 4 lines.
If what you really want is to have a total for each Dept, then what I'm doing won't work You would be better off learning about Pivot Tables for that kind of report.

[foxguy]

Hi Ray;

I think we need to back up a step. I am not convinced that what I have been trying to do is your best solution.
When you started adding in new conditions, I made the mistake of trying to fit the previous solution to the new conditions. Instead, we should have backed up and taken a look at all the conditions and figured out what the best solution is.
You originally said you were looking to be able to read the data without having to look across a big spreadsheet to find the data. Now you're adding in the condition that the columns may change from time to time.
I've just about convinced myself, that you would be better off if I designed a sheet with the formulas in them, rather than a macro that counts your cars. The sheet will be a lot easier for you to modify to fit changing conditions in the future. You have already discovered that you can't just go into a macro and change something that you don't understand. You won't have that same problem with formulas.

You will get a much better solution, if you describe your complete situation. In particular why your raw data may have different columns, and why you may not want all the columns in your reports.

[Ray789]

Hi Foxguy,

Let me respond to your previously post first and then I will have a look at todays post:

Your assumption is correct, whatever order the columns are in Raw Data should be the same
in the report. I.e. Name, Dept, Address in Raw Data would be the expected order in the report too. This is actually quite key.

I understand your last point. From my perspective I can always select the Name from the Output Form before a report is ran.

If you need to use the 'Name' column for the code please do. This column will probably change
to First Name and Last Name after everything is done.

[Ray789]

Hi Foxy,

I will try to provide a Summary of what I wanted from the Raw Data and the Outputs:

1 – Output showing Owners and the Cars that belong to them so I didn’t have to look across a huge spreadsheet. This included a summary of all Cars for the owner in one cell too.

2 – Output showing the status of where each of the Cars were by owner depending on preset conditions i.e. On Lease, In Repair, Available.

This was all achieved on one sheet in ‘Copy of Sample 2 - Lookups.xls’ using formulae.

I then asked if this could be done using a Macro which was done on Sample 2 - Lookups.xls which was great.

3 – I then asked if Output1 and Output 2 could be displayed on separate sheets with the conditions being on a separate sheet too (Sheet4) – This was also achieved.

4 – For output2 which showed the status of each Car numerically i.e. On Lease, Repair etc. I wanted a sheet which showed all the same headings as ouput2 however where the numbers appeared I wanted the name of the Cars in Sheet5. – I have not seen this yet.

5 – I wanted an unknown column for cars which did not fall under a condition for Output2. This would be the same for point4. – This was also achieved in Copy of Sample 2 - Lookups Ver 3.xls

6 – Wanted the ability to add columns from the descriptive data i.e. Name, Department etc – This has also been achieved by using the check boxes in Copy of Sample 2 - Lookups Ver 4.xls.

In essence I think you sorted all of it out with the exception of point 4 in various outputs you provided. I think we are nearly there to be honest and the Macros have been doing everything so far. It would be a shame to back to fomulaes because I do find using the Macros much easier to use.

[foxguy]

Hi Ray;
Sorry it took so long, it seems like I've been sleeping for a solid 48 hours. I can't wait to get rid of this medication. But that's my problem, back to yours.

You misunderstood me.

I wasn't looking for a recap of what you asked for. I was looking for a description of what you're trying to accomplish. The initial message just said "I want this in these cells so I don't have to look across a big spreadsheet to find the data", but now you want to have different columns in the output, etc.

It is almost a guaranteed certainty that you will want to improve the workbook in the future. The more I do for you, the harder it will be for you to improve it. You already discovered that you can't just go into a macro and delete a line. So I'm trying to design something that will allow you to modify it without help from me.
I know that the macro "Count_Cars" can be replaced completely by formulas (which will be a lot easier for you to modify in the future), but I think I would need a better description of what you're trying to accomplish in order to get rid of it

Without a better description of your actual problem, here's the best I think I can do. Copy of Sample 2 - Lookups Ver 4-4.xls
I have put in a sheet with instructions on how to put all this into your workbook.
There is no button to run "Count_Cars", because I didn't know where you would want it. You can put a button in your own workbook and just assign the macro "Count_Cars" to it.
If you would rather have a menu button to run the macro, and don't know how to put in a temporary menu button, let me know.
I tried to think of a way to have the macro run automatically, but I couldn't figure out where to put it. i.e. when you change a certain cell, it would automatically run, but I would need a better description of what you're trying to accomplish in order to run it automatically.

[Ray789]

Hi Foxguy,

The spreadsheet seems to be working fine and thanks for the instructions. I hope you feel better soon.

The only thing I don't like is the manual updating of descriptive data columns i.e.If I add a number of new columns before the 'Cars' column in Raw Data I need to manually drag the formulae down after the Macro has been run.

The idea is I have the Raw Data and I send it out to a number of 3rd Party Suppliers who then run the Macro which gives them the Descriptive Data plus the columns which need counting aligned together so they don't have to look across a huge spreadsheet (Output1).

I also want to give them an option of viewing what the status of each Car is (Output2). They would then look to see who the employee is and what department etc... they fall under.

Further to this they will from time to time want to know which Cars have gone in for Repair (Output3) and again would look at the descriptive data for more information. This output would be an option so they can decide if they want to run it or not.

I can't really see them doing the manual task of dragging the formulae. I am not too precious about having a button, but if I did need one I would probably have it on Raw Data cell A1 as this is the source tab they would look at first.

Hope that provides some clarification.

Your a Star mate!!

[foxguy]

Hi Ray;

That helps quite a bit.

Do the suppliers have the Raw Data sheet? I assume they do, because the Output sheets need the data on the Raw Data sheet for the descriptive columns.
You say that the suppliers run the macro. Why don't you run it before sending out the file? That way all 3 outputs are already available and they just select the sheet that they want to look at.

If I was doing it, I would put all the output on the Raw Data sheet to the right of the data to count. I would then provide buttons to select one of the outputs to view (all the unselected outputs would be hidden). This would save having to worry about copying formulas or running macros (all the outputs would be formulas, so there would be no macros to run).
I would also include instructions on how to hide the descriptive columns that they don't want to see.

If you want to leave the different outputs on different sheets, then I would create a macro that creates the right # of columns and rows on each of the output sheets. I probably should have included it in the file I uploaded, so here it is:

Please Login or Register  to view this content.

When you change the # of rows or columns in Raw Data, then just run this macro. It will delete all the data on the output sheets, so you will want to run "Count_Cars" afterwards.

You can have it run every time that Count_Cars runs if you want. Just insert this line inside "Count_Cars"

Please Login or Register  to view this content.

[Ray789]

Hi Foxguy,

I have been away for a while so apologies for a late response.

Yes the suppliers will have the Raw Data. I would always like to run the Macro first however it would be good if you had a choice of which one you want running. If this means disabling some of the code that's fine.

I did think of having the output to the right of the raw data first however the sheet is already long enough so did not want anyone straining their neck by adding further columns to the right.

The code seems to be working fine so thankyou you have been brilliant. You may want to attach a copy here so others can download it.

[foxguy]

Hi Ray;

I'm in the process of reviewing your workbook. I can't remember how I did things, so it may take a while.

[foxguy]

Hi Ray;

I can't see that anything is waiting to be done. Are you waiting for anything?

I remember calculating all 3 outputs, and the user just selects the sheet they want to look at whichever sheet they want.

I can't see any reason to not calculate all 3. It doesn't take any more time and that way you don't have to worry about them getting out of sync with each other.

The code seems to be working fine so thankyou you have been brilliant. You may want to attach a copy here so others can download it.

The code is pretty specific to your situation. If anyone wants the file itself, they can ask. No point it using up more of the forum's server space.