need help pulling data from 3 tabs into 1 new tab using Excel functions. Prefer not to use VB.

patrickws

New Member
Joined
Sep 10, 2014
Messages
11
Good day,

I need help pulling data from 3 tabs (Host, IP, Drive) into 1 new tab (Final Output) using Excel functions. Prefer not to use VB.

Data on Host, IP and Drive tab is pulled using ODBC. Data is automatically updated and refresh as new records are added / changed in the source database. Some records on IP and Drive tab will have multiple records for the same host (ServerX, ServerY, ServerZ).

Desired results:
The Final Output tab would have a row for every line of data from Host, IP and Drive tab.
The Final Output tab would dynamically update as new data was added / changed on Host, IP or Drive tab.

OS: Windows 7
Excel 2007

Thanks.



Mr_Excel_9_10_14.png
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
patrickws,

You are posting a picture/graphic. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.


Can we see screenshots of the three actual raw data worksheets, and, what the results should look like?


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots using one of the above two programs:

You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
patrickws,

Thanks for the workbook.

I know that you said you did not want to use VBA, but, just in case a formulae solution is not posted:


With all your raw data worksheets sorted/grouped in column A by S_ID# per you sample workbook:


Sample worksheets:


Excel 2007
ABCD
1S_ID#HOSTMAKEMODEL
21ServerXDell123
32ServerYHP321
43ServerZIBM213
5
Host



Excel 2007
ABC
1S_ID#IPSUBNET
2110.10.10.10255.255.255.0
3110.10.10.11255.255.255.0
4210.10.10.12255.255.255.0
5310.10.10.13255.255.255.0
6310.10.10.14255.255.255.0
7310.10.10.15255.255.255.0
8310.10.10.15255.255.255.0
9
IP



Excel 2007
AB
1S_ID#DRIVE
21100GB
31400GB
411000GB
511500GB
62100GB
72500GB
821000GB
93100GB
103700GB
11
Drive



Excel 2007
ABCDEFG
1
2
3
4
5
6
7
8
9
10
11
12
13
Final Output


After the macro in worksheet Final Output:


Excel 2007
ABCDEFG
1S_ID#HOSTMAKEMODELIPSUBNETDRIVE
21ServerXDell12310.10.10.10255.255.255.0100GB
31ServerX10.10.10.11255.255.255.0400GB
41ServerX1000GB
51ServerX1500GB
62ServerYHP32110.10.10.12255.255.255.0100GB
72ServerY500GB
82ServerY1000GB
93ServerZIBM21310.10.10.13255.255.255.0100GB
103ServerZ10.10.10.14255.255.255.0700GB
113ServerZ10.10.10.15255.255.255.0
123ServerZ10.10.10.15255.255.255.0
13
Final Output


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub CreateFinalOutput()
' hiker95, 09/10/2010, ME804660
Dim wh As Worksheet, wi As Worksheet, wd As Worksheet, wf As Worksheet
Dim c As Range, nr As Long, i As Range, d As Range
Dim nip As Long, ndr As Long, mn As Long
Application.ScreenUpdating = False
Set wh = Sheets("Host")
Set wi = Sheets("IP")
Set wd = Sheets("Drive")
Set wf = Sheets("Final Output")
With wf
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(, 7).Value = Array("S_ID#", "HOST", "MAKE", "MODEL", "IP", "SUBNET", "DRIVE")
End With
With wh
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    nip = Application.CountIf(wi.Columns(1), c.Value)
    ndr = Application.CountIf(wd.Columns(1), c.Value)
    mn = WorksheetFunction.Max(nip, ndr)
    nr = wf.Cells(wf.Rows.Count, "A").End(xlUp).Row + 1
    wf.Cells(nr, 1).Resize(, 4).Value = .Cells(c.Row, 1).Resize(, 4).Value
    wf.Cells(nr, 1).Resize(mn, 2).Value = .Cells(c.Row, 1).Resize(, 2).Value
    Set i = wi.Columns(1).Find(c.Value, LookAt:=xlWhole)
    wf.Cells(nr, 5).Resize(nip, 2).Value = wi.Cells(i.Row, 2).Resize(nip, 2).Value
    Set d = wd.Columns(1).Find(c.Value, LookAt:=xlWhole)
    wf.Cells(nr, 7).Resize(ndr).Value = wd.Cells(d.Row, 2).Resize(ndr).Value
  Next c
End With
With wf
  .UsedRange.HorizontalAlignment = xlCenter
  .UsedRange.Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CreateFinalOutput macro.
 
Last edited:

patrickws

New Member
Joined
Sep 10, 2014
Messages
11

ADVERTISEMENT

Hey hiker95, thanks for the script, it works fine. I appreciate your time.
Hopefully there is a way to do the same thing using Excel functions. So the question is, are there any Excel gurus on this forum that know how to do it? :p
Thanks.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
patrickws,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.


Hopefully there is a way to do the same thing using Excel functions. So the question is, are there any Excel gurus on this forum that know how to do it?

To keep your thread active:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
 

patrickws

New Member
Joined
Sep 10, 2014
Messages
11
BUMP
Hopefully there is a way to do the same thing using Excel functions. So the question is, are there any Excel gurus on this forum that know how to do it?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,374
Messages
5,836,888
Members
430,460
Latest member
cristian270

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top