Extract details from .xls database onto another .xls

December 20, 2011 at 04:54:44
Specs: Windows Vista
Hi,

Was wanting some help if possible, please?

Im trying to extract details of a value from an excel db. into a cell within another .xls (results .xls)

The db is very comprehensive and can contain the desired value within many cells.
My intention is to extract the value (although it may appear multilpe times on each row)
and to display the total number of that value for each row e.g.

desired value= a
'a' appears 5 times (column a, b, c, d, e) within row '1'
so the total displayed in the results .xls should equal 1

'a' appears 10 times in row 2
so the total displayed in results.xls should equal 2 (this is the total from row 1 & 2)

Hope this makes sense? and more importantly is this possible?


See More: Extract details from .xls database onto another .xls

Report •


#1
December 20, 2011 at 06:21:36
Your question is confusing.

You say that you want "to display the total number of that value for each row " which sounds like you would want 5 for Row 1, 10 for Row 2, etc.

It looks to me like "the total from row 1 & 2" should be 15, not 2.

Are you really just asking for the total number of rows in which the value appears, regardless of how many times it appears in a given row? That's the only way I can see a result of "2" based on your example.

We also need to know some other information.

When you say "extract details of a value from an excel db" do you mean an Excel workbook, i.e. a .xls file? Is this a separate file from results.xls or just another worksheet in the same file?

Where in results.xls do you want the information placed?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 20, 2011 at 07:26:13
Many thanks for your prompt reply. In response to your questions:

Are you really just asking for the total number of rows in which the value appears, regardless of how many times it appears in a given row? That's the only way I can see a result of "2" based on your example

- This is correct

When you say "extract details of a value from an excel db" do you mean an Excel workbook, i.e. a .xls file? Is this a separate file from results.xls or just another worksheet in the same file?

- I do mean an .xls file, this is seperate to 'result.xls'

Where in results.xls do you want the information placed?

- Ideally the formula will be used in many cells, but it would be to pre-defined cells within 'results.xls'

Hope this makes sense.

Thank you in advance for your assistance : )


Report •

#3
December 29, 2011 at 07:34:14
surely if you have both spreadsheets open at the same time and create a formula that references the source database in the target .xls and either re-create all the possible cells of interest in the target xls then it only requires something like - "=SUM(F6:N6)"

or better still try help and search for "inserting link"

it will guide you to something like this

=SUM([Schedules4.xls]Messages!$K$8,[Schedules4.xls]Messages!$N$8)

though I have to say my example used xls's in the same folder.

blank cells return a value of 0 in my experience.


Report •

Related Solutions

#4
December 29, 2011 at 10:32:44
This User Defined Function (UDF) will search each row for the value entered as k and increment a counter if it finds that value in the row.

Once it's done searching, it will return the total number of rows in which the value was found.

Modify as required.

Function FindValue(k As String)
 lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
   For rw = 1 To lastRw
     With Sheets(1).Rows(rw)
      Set c = .Find(k, lookat:=xlWhole)
       If Not c Is Nothing Then tmpCount = tmpCount + 1
     End With
   Next
 FindValue = tmpCount
End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question