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

[patrickws]

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.

 

[hiker95]

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.

 

[patrickws]

Thank you for the information hiker95.

This is the link to the file:
https://app.box.com/s/f4u5ejfw1t1l6qntsivk

Thanks.

 

[hiker95]

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
	A	B	C	D
1	S_ID#	HOST	MAKE	MODEL
2	1	ServerX	Dell	123
3	2	ServerY	HP	321
4	3	ServerZ	IBM	213
5
Host

Excel 2007
	A	B	C
1	S_ID#	IP	SUBNET
2	1	10.10.10.10	255.255.255.0
3	1	10.10.10.11	255.255.255.0
4	2	10.10.10.12	255.255.255.0
5	3	10.10.10.13	255.255.255.0
6	3	10.10.10.14	255.255.255.0
7	3	10.10.10.15	255.255.255.0
8	3	10.10.10.15	255.255.255.0
9
IP

Excel 2007
	A	B
1	S_ID#	DRIVE
2	1	100GB
3	1	400GB
4	1	1000GB
5	1	1500GB
6	2	100GB
7	2	500GB
8	2	1000GB
9	3	100GB
10	3	700GB
11
Drive

Excel 2007
	A	B	C	D	E	F	G
1
2
3
4
5
6
7
8
9
10
11
12
13
Final Output

After the macro in worksheet Final Output:

Excel 2007
	A	B	C	D	E	F	G
1	S_ID#	HOST	MAKE	MODEL	IP	SUBNET	DRIVE
2	1	ServerX	Dell	123	10.10.10.10	255.255.255.0	100GB
3	1	ServerX			10.10.10.11	255.255.255.0	400GB
4	1	ServerX					1000GB
5	1	ServerX					1500GB
6	2	ServerY	HP	321	10.10.10.12	255.255.255.0	100GB
7	2	ServerY					500GB
8	2	ServerY					1000GB
9	3	ServerZ	IBM	213	10.10.10.13	255.255.255.0	100GB
10	3	ServerZ			10.10.10.14	255.255.255.0	700GB
11	3	ServerZ			10.10.10.15	255.255.255.0
12	3	ServerZ			10.10.10.15	255.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.

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.

 

[patrickws]

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?
Thanks.

 

[hiker95]

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]

bump

 

[patrickws]

bump

 

[patrickws]

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?