Using multiple Lookup Functions

[Gaura215]

Respected Excel Gurus

I spent nearly 2 hours daily on updating a report from a dump, which I download from a website. I am wonderin if this can be automated using some formulaes or Macro. Please help me out.

I can share the 2 spreadsheets which I need to compare. Please advice, if someone can help me with this. Its difficult to explain it in words without sharing the spreadsheets. So I will share the spreadsheet with whatever explanation I can give.

Help please

 

[Peter_SSs]

Its difficult to explain it in words without sharing the spreadsheets.

You should try, and you can post small screen shots using a variety of methods - my signature block below contains several suggestions.

Being a public forum we like to keep the information/conversation "on-forum" as much as possible. Related points are 18 & 19 of the Posting Guidelines and #7 of the Forum Rules.

Apart from the above, you will get more potential helpers if you put your problem and any related information, sample data etc directly in your posts.

 

[Gaura215]

Sure Peter, I have attached the 2 JPEG snapshots of both the reports. I will try to explain now the requirements:

1. Dump Report is the report, which I download everyday from a website, and I get to know the status(Coloum F) of that activity(Coloum A) of a particular country (Coloum D);
2. Using the report as mentioned in Step 1, I need to fill in the status in Final Report from Coloum D onwards.

So the activity name which were in Coloum A in Dump Report (Step1 above) are in Row 6 horizontly in Final Report. And the country name which were in Coloum D in Dump report are in Coloum B of Final Report.

I am looking for a code, which will match these two conditions, and show the status of that activity (Coloum F of Dump Report) in final report.

Hope I have been able to explain the requirement. Please let me know if more clarifation is required in this.

I will be extrmely thankful to everyone who can spend their precious time in helping me out.

Thanks a ton in anticipation.

Regards
Gaura215

 

[Peter_SSs]

Sure Peter, I have attached the 2 JPEG snapshots of both the reports.

No doubt you have now realised that you cannot insert images that are stored on your own computer.
It is possible to store an image on a public file-sharing site and provide a link to that. The drawback with that is that a potential helper cannot copy your sample data to test. Most helpers will not bother typing a lot of sample data to test.

On the other hand, the methods in my signature block that I referred to, do allow a user to copy the data to a worksheet to test and that is much more inviting to a potential helper.

 

[Gaura215]

Final Report Looks Like Following:

		Distribute flash to managing director/ finance director	Standard monthly journals	Depreciation run	Fixed asset close	Revenue journals and adjustments	FX revaluation	Receive 107 and book journal	Input outbound profit share to HFM
Country	POC / Due Date	WD 1	WD 1	WD 1	WD 1	WD 2	WD 2	WD 2	WD 2
Austria	Martina Meuhauser
Belgium	Erik van Zuilen
Czech Republic	Izabela Handzlik
Finland	Darren Aldous
France	Guillaume Brun
Germany	Marco Mischon
Hungary	Andrea Pasztor

<colgroup><col style="width: 104pt; mso-width-source: userset; mso-width-alt: 5046;" width="138"> <col style="width: 124pt; mso-width-source: userset; mso-width-alt: 6034;" width="165"> <col style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" width="68"> <col style="width: 31pt; mso-width-source: userset; mso-width-alt: 1499;" span="3" width="41"> <col style="width: 36pt; mso-width-source: userset; mso-width-alt: 1755;" width="48"> <col style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;" width="44"> <col style="width: 36pt; mso-width-source: userset; mso-width-alt: 1755;" span="2" width="48"> <tbody>
</tbody>

Dump Report Looks like following:

Task Name	Description	Country Name	Status Stage
Fixed asset close	WD1	Italy	On Schedule Completed
Depreciation run	WD1	Italy	On Schedule Completed
Distribute flash to managing director/ finance director	WD1	Hungary	On Schedule Completed
Fixed asset close	WD1	Hungary	On Schedule Completed
Depreciation run	WD1	Hungary	On Schedule Completed
Depreciation run	WD1	Germany	On Schedule Completed
Fixed asset close	WD1	Germany	On Schedule Completed
Fixed asset close	WD1	Sweden	On Schedule Completed
Depreciation run	WD1	Sweden	On Schedule Completed
Fixed asset close	WD1	Belgium	On Schedule Completed
Distribute flash to managing director/ finance director	WD1	Belgium	On Schedule Completed
Depreciation run	WD1	Belgium	On Schedule Completed
Standard monthly journals	WD1	Netherland	On Schedule Completed
Fixed asset close	WD1	Netherland	On Schedule Completed
Distribute flash to managing director/ finance director	WD1	Netherland	On Schedule Completed
Depreciation run	WD1	Netherland	On Schedule Completed
Standard monthly journals	WD1	Belgium	On Schedule Completed
Standard monthly journals	WD1	Finland	On Schedule Completed
Depreciation run	WD1	Finland	On Schedule Completed
Fixed asset close	WD1	Finland	On Schedule Completed

<colgroup><col style="width: 48pt;" span="4" width="64"> <tbody>
</tbody>

 

[BlueHornet]

Let's assume that your "final report" formatted sheet starts at A1, so C5 is the first cell of the data that you need to be collecting. And we'll also assume that your data dump occurs on a sheet that we'll call "Data", starting at A1, so the range we're looking at could be A1:D1000 (could easily be more).

Then at C5 of your report sheet enter this formula:
=sumproduct(( data!$A$1:$A$1000 = C$1) * ( data!$C$1:$C$1000 = $A5))

This will give you the count of "Distribute flash to managing director (etc.) for Austria". If you copy that cell down for all countries and across for all actions, then you'll have the intersecting counts for each item / country.

This doesn't address the "status stage" datum, but presumably you have another report for that (or following this logic, you can also include that datum).

 

[BlueHornet]

On the other hand, if you have unique relationships set up between "country" & "activity" & "status", then that count is going to be meaningless, since all you want to know is the status for each activity in each country. So, a different approach:

1. In the "data" worksheet, include a column to the left of your four current data columns, and in that column enter the formula:
=D2 & B2
This is to concatenate the country & activity like so: "ItalyFixed asset close", and so on. Copy the formula down through all of your data rows.

2. On the "report" sheet at C5, use this formula:
= vlookup( $a5 & c$1, data!$a$2:$e$1000, 5, false) and copy that formula through all of your report cells.

Is that what you wanted? With the limited data set you provided (and thanks for using Peter's advice to present this in a readable way!), that seems to work from what I'm seeing, but you're the judge.

 

[Gaura215]

Hi BleHornet

Thanks a lot for taking time and helping me out on this. Howeverm the count will remain same for all cells, as the report always have same count. The Status Stage is what keep on changing and which is to be reported.

Is their a way to report the status stage?

 

[BlueHornet]

Presumably you've seen my second response as well by now.

 

[Peter_SSs]

1. Could you make clear what columns are shown in your tables? For example your Dump Report table has 4 columns but your description of it in post #3 refers to columns A, D and F in that report.

2. Could you show us the final report with the expected results for the sample data shown together with any further explanation about how the results would be calculated?