CODEWITHSUNDEEP
excelreport
user1527584
user1527584

Reputation: 11

Excel formula by combining 2 sheets

I need help in generating excel report.Can anyone of you please help me.

I have 2 excel files. I have tried to paste the files in the question.

file1:

Column A                    Column B        Column C
----------------------------------------------------
$www.example1.com/ab        200             abc

file 2:

URL                              Hits
-----------------------------------------
$www.something.com/dir/abc       1000
$www.example1.com/ab             100
$www.example2.com/cd             50
$www.example1.com/ab             100
  1. Contains 3 columns -- colA (URLs), colB(Hits in Numerals), colC(some data)
  2. Contains 2 columns -- ColA (URLs), ColB(Hits in Numericals)

Steps:

  1. Take ColA(URLs) from file1 and search in ColA(URL) of files2.
  2. Suppose we get 10 searches, I need to get the Sum of all the ColB(Hits) of file2 and place it in file1 ColB of the first result.

Any kind of hints would be helpful. I tried many options, but none of them worked.

Upvotes: 1

Views: 83

Answers (1)

JackDMF
JackDMF

Reputation: 70

Should be possible under the following conditions:

  • Both Files are open
  • the URLs are the same

Then use code similar to this example:

=SUMIF([Name of file 2]NameOfSheet!$A$2:$C$6;A2;[Name of file 2]NameOfSheet!$B$2:$B$6)

Where $A$2:$C$6 is the range of data in file 2 and A2 is the cell with the value in file 1 and $B$2:$B$6 is the range of data to be summed up within file 2.

Hope this helps.

Upvotes: 1

Related Questions