Hello:

I have the problem how to re-format the data. I have attached the
sample sheet. The original data was in Sheet-A and already define the
name as "Master". May I know how can I make it work on Sheet-B?

Sheet-A Original Data:
Host
Category
ItemName
ItemValue1
OAP004
System Overview
Domain Name
DOMAIN1
OAP004
System Overview
Operating System
Microsoft(R) Windows 200 Standard Edition
OAP004
Network TCP/IP
IP Address
192.168.11.26


Sheet-B Re-format Data:
Host
Domain Name
Operating System
IP Address
OAP004
DOMAIN1
Microsoft(R) Windows 200 Standard Edition
192.168.11.26



The First row is the header.

Thanks a lot!

I think a little bit more information would be helpful. Will there be other Host names on Sheet-A (with their own unique information)? How is the Host name getting into Sheet-B... are you typing it and wanting the other information looked up or, if there will be more than one Host, are you wanting all the Host names populated on Sheet-B automatically? For a single Host name, will there **always** be three rows of information on Sheet-A? Will the rows of information on Sheet-A **always** be in the same order (for each Host)?

Rick

"Blue Fish" wrote in message ...
Hello:

I have the problem how to re-format the data. I have attached the sample sheet. The original data was in Sheet-A and already define the name as "Master". May I know how can I make it work on Sheet-B?

Sheet-A Original Data:
Host
Category
ItemName
ItemValue1

OAP004
System Overview
Domain Name
DOMAIN1

OAP004
System Overview
Operating System
Microsoft(R) Windows 200 Standard Edition

OAP004
Network TCP/IP
IP Address
192.168.11.26


Sheet-B Re-format Data:
Host
Domain Name
Operating System
IP Address

OAP004
DOMAIN1
Microsoft(R) Windows 200 Standard Edition
192.168.11.26



The First row is the header.

Thanks a lot!

Sorry for the information not enought. Yes, it will have another
hostname. The hostname in Sheet-B is Manual copy. For the Hostname in
Sheet-A, it will have more than 3 rows. The order of Sheet-A will not
have the same order but the Hostname I will sort in order.

Thanks!

Rick Rothstein (MVP - VB) wrote:
I think a little bit more information would be helpful. Will there be
other Host names on Sheet-A (with their own unique information)? How
is the Host name getting into Sheet-B... are you typing it and wanting
the other information looked up or, if there will be more than one
Host, are you wanting all the Host names populated on Sheet-B
automatically? For a single Host name, will there **always** be three
rows of information on Sheet-A? Will the rows of information on
Sheet-A **always** be in the same order (for each Host)?

Rick


"Blue Fish" wrote in
message ...
Hello:

I have the problem how to re-format the data. I have attached
the sample sheet. The original data was in Sheet-A and already
define the name as "Master". May I know how can I make it work on
Sheet-B?

Sheet-A Original Data:
Host
Category
ItemName
ItemValue1
OAP004
System Overview
Domain Name
DOMAIN1
OAP004
System Overview
Operating System
Microsoft(R) Windows 200 Standard Edition
OAP004
Network TCP/IP
IP Address
192.168.11.26


Sheet-B Re-format Data:
Host
Domain Name
Operating System
IP Address
OAP004
DOMAIN1
Microsoft(R) Windows 200 Standard Edition
192.168.11.26



The First row is the header.

Thanks a lot!

On Sheet-B, put the following formulas in the indicated cells and copy them down as necessary...

B2: =IF(ISNUMBER(MATCH(A2,'Sheet-A'!A:A,0)),INDEX('Sheet-A'!D:D,SUMPRODUCT(('Sheet-A'!A$2:A$3000=A2)*('Sheet-A'!C$2:C$3000="Domain Name")*ROW(A$2:A$3000))),"")

C2: =IF(ISNUMBER(MATCH(A2,'Sheet-A'!A:A,0)),INDEX('Sheet-A'!D:D,SUMPRODUCT(('Sheet-A'!A$2:A$3000=A2)*('Sheet-A'!C$2:C$3000="IP Address")*ROW(A$2:A$3000))),"")

D2: =IF(ISNUMBER(MATCH(A2,'Sheet-A'!A:A,0)),INDEX('Sheet-A'!D:D,SUMPRODUCT(('Sheet-A'!A$2:A$3000=A2)*('Sheet-A'!C$2:C$3000="Operating System")*ROW(A$2:A$3000))),"")

As long as the columns in the rows are laid out as you have shown them, you do not have to sort the row order for these formulas to work. One note though, as constructed, the formulas will only work for data up to Row 3000... if you will have data in rows higher than 3000, you will have to change the 3 occurrences of 3000 in *each* formula to the highest row number you will ever expect to have data in.

Rick


"Blue Fish" wrote in message ...
Sorry for the information not enought. Yes, it will have another hostname. The hostname in Sheet-B is Manual copy. For the Hostname in Sheet-A, it will have more than 3 rows. The order of Sheet-A will not have the same order but the Hostname I will sort in order.

Thanks!

Rick Rothstein (MVP - VB) wrote:
I think a little bit more information would be helpful. Will there be other Host names on Sheet-A (with their own unique information)? How is the Host name getting into Sheet-B... are you typing it and wanting the other information looked up or, if there will be more than one Host, are you wanting all the Host names populated on Sheet-B automatically? For a single Host name, will there **always** be three rows of information on Sheet-A? Will the rows of information on Sheet-A **always** be in the same order (for each Host)?

Rick

"Blue Fish" wrote in message ...
Hello:

I have the problem how to re-format the data. I have attached the sample sheet. The original data was in Sheet-A and already define the name as "Master". May I know how can I make it work on Sheet-B?

Sheet-A Original Data:
Host
Category
ItemName
ItemValue1

OAP004
System Overview
Domain Name
DOMAIN1

OAP004
System Overview
Operating System
Microsoft(R) Windows 200 Standard Edition

OAP004
Network TCP/IP
IP Address
192.168.11.26


Sheet-B Re-format Data:
Host
Domain Name
Operating System
IP Address

OAP004
DOMAIN1
Microsoft(R) Windows 200 Standard Edition
192.168.11.26



The First row is the header.

Thanks a lot!

Thanks a lot for your kindly help. It was quite useful for me!

For remove the limitation of the 3000. Can I use "Sheet-A'!C:C"?

If you are using XL2003 or earlier, no... array functions can't use a full
row or column reference in XL2007 you can use it, but you probably
shouldn't... making the array iterate over all the rows in the column is not
as efficient as limiting the iterations over the maximum number of rows that
will ever be used.

Rick

"Blue Fish" wrote in message
...
Thanks a lot for your kindly help. It was quite useful for me!

For remove the limitation of the 3000. Can I use "Sheet-A'!C:C"?

Hi Rick,

Thanks a lot for your information. It was working on my excel with
your formula. I am trying to digest what is the function doing.

Thanks a lot!

Rick Rothstein (MVP - VB) wrote:
If you are using XL2003 or earlier, no... array functions can't use a
full row or column reference in XL2007 you can use it, but you
probably shouldn't... making the array iterate over all the rows in
the column is not as efficient as limiting the iterations over the
maximum number of rows that will ever be used.

Rick