?
Solved

How sort and match multiple Excel rows to one row

Posted on 2007-12-01
8
Medium Priority
?
1,756 Views
Last Modified: 2012-06-27
Ok Excel Guru's
I have 4 columns of data. Each column is a list (audit) of the computer names on my network.
The rows are from AD, SAV, WSUS, and a Network Security Scan Tool.
Some computer names are on all the columns. Some are only in one or two of the columns. Each column of data has a different number of entires. Some over a hundred entires difference. So sorting is out of the question. Is there a way to sort all four rows to the first (largest) row. Have all matching entires in the columns sorted next to its match in the first column. Where there is no name match, the cell would be colored red to show where there is a mis-match.

Now,  if there is an entry in one of the other columns. Take for instance the Network scan picks up a Computer name but it isnt in AD. A row in the AD coloum would turn red. And visa vera. I walked into a bad situation and I need a way to organize this data.

If it can not be done in Excel. Can someone please let me know any other formats or programs that I can use to format this data this way.
0
Comment
Question by:cutzpr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20388421
Hello cutzpr,

It would be useful to see some sample data.  Please upload
a sample file to an accessible web site, and come back
here to post a link to the file.

If you do not have your own web space, you can use
http://www.ee-stuff.com.  Please be advised that you
will have to zip your file(s) before uploading if you use that
service.

Regards,

Patrick
0
 

Author Comment

by:cutzpr
ID: 20390224
I have modified some data and posted it. Just copy and paste into a .txt, then rename it into a .csv and you should be able to open it in excel with no problem.
105QMD02AC,98xxyy9F83,015oooeeec,98xxyy8113
2564xy1E36,98xxyy8113,2564xy1e36,abccav19b1
700mmmE6C5,98xxyy683B,2564xy4ac9,abcmsyc1a5
98xxyy9FCF,98xxyy3341,2564xya559,abcs10c6dd
546MOT4030,98xxyy27C9,105qmd02ac,98xxyy27b5
98xxyy683B,98xxyy1335,105qmd54a4,abckkke449
98xxyy27C9,834POSDD6A,700mmm1685,abchum002d
98xxyy8113,834POS3386,700mmme6c5,546mot4030
35Sbvc9BEF,732ghgA02D,700mmme6de,abctoc2533
oyyTCFC55C,732ghg24A0,35sbvc615f,abckkk6db3
PMJ-E6CB03DE09F,732ghg242A,35sbvc9bef,732ghga02d
635mmm1E37,546MOT4030,35sbvcafc7,abcs20849d
98xxyy27B5,546MAT027F,35sbvcf98f,abcmsy63df
98xxyy8258,abcXO02C70,abcjew2d0a,abcjew4fb3
oyyOSPA6DD,abcTOCEC5D,abcjew38e2,abcchpb336
700mmmE6DE,abcTOC2533,abcjew4fb3,546mat027f
98xxyy1335,abcSUPE33A,abcjewa093,abcbtp75ba
110car53FA,abghgOB60D,abcjewa249,abcbtp4a8d
35Sbvc615F,abghgO2519,abcjewa2e9,abcjewa2e9
732ghgA02D,abcS6OB28C,abcjewac64,834pos3386
DCMA001999,abcS60C727,abcjewe770,110unc179e
2564xyA559,abcS60A3F0,abcbtp18bd,abckkke7d8
834POSDD6A,abcS6041BC,abcbtp2535,abckkk92d1
110car1578,abcS50FEA4,abcbtp4a8d,abcqwecc42
NAVEOD20BA,abcS50F02F,abcbtp75ba,abchht436e
N635mmm4E0F,abcS50DBF6,abcbtp9f78,abckkka20d
635mmm4E0F,abcS50271A,abcbtpa02a,abcs50eba2
35SbvcAFC7,abcS40C36B,abcbtpc47c,oyyospa6dd
732ghg24A0,abcS407F45,abcbtpcd19,abcs20c632
732ghg242A,abcS404B93,abcbtpe158,abcqwea289
110carCCC4,abcS402BEF,abcbtpe559,466denb3f7
35SbvcF98F,abcS30B691,abccav19b1,abcs10266f
700mmm1685,abcS30A197,abccave1cb,abcqwea01b
635mmmE889,abcS30810F,abcchpb336,abcewoc492
110car2DDB,abcS307E84,abccsm4dac,abcmed66d4
546MAT027F,abcS3031C3,abcqwe4cec,abckkk9940
834POS3386,abcS20C632,abcqwe761c,abckkk6ae1
MEDOPS6EE0,abcS20849D,abcqwea01b,abcs10a362
700mmmFB0B,abcS202886,abcqwea289,abcs60c727
98xxyy9F83,abcS10C6DD,abcqwec8f8,86sbvc18d9
466DENB3F7,abcS10B660,abcqwecb6f,abcuavb5da
110unc179E,abcS10A362,abcqwecc42,700mmme6de
2564xy4AC9,abcS1035FC,abcqwed088,abcbtpa02a
105QMD54A4,abcS102B9D,abcqwefc9a,abckkkf3c4
635mmmF702,abcS10266F,abcdisd98b,abghgo2519
98xxyy3341,abcS100659,abckkk3085,abckkkcc7f
110car6805,abcRAD8991,abckkk47bd,700mmme6c5
2564xyFE0E,abcPAODC2D,abckkk6ae1,98xxyy27c9
abcMEDA58E,abcMED94A1,abckkk6db3,abcsupe33a
abcS5OA43C,abcMED7E63,abckkk795e,35sbvcf98f
98xxyy431D,abcMED66D4,abckkk92d1,635mmm1e37
abcTMC247F,abcmsyC1A5,abckkk9940,abcxo02c70
NAVEOD3C13,abcmsy63DF,abckkka20d,2564xy1e36
abcMEDE3BA,abcmsy427E,abckkkcc7f,abccsm4dac
abckkkC627,abcJNNC216,abckkkd08b,abcs6ob28c
abckkk25E4,abcHUM002D,abckkkd0ef,abcs30810f
abckkkE2F4,abcHHTB52E,abckkke360,abcrad8991
abcDISCBF8,abcHHT436E,abckkke449,abghgob60d
abcMED2BE9,abcEWOC492,abckkke7d8,732ghg242a
86SbvcB3E6,abckkkF3C4,abckkkeffc,732ghg24a0
abcSUPE159,abckkkEFFC,abckkkf3c4,abcs50f02f
abckkkE157,abckkkE7D8,abcewoc492,35sbvc615f
466TMC2474,abckkkE360,abcfao4c5e,abcs6041bc
ooo-6E1356,abckkkD0EF,abchht436e,abcbtp18bd
ooo-A518E6,abckkkD08B,abchhtb52e,635mmme889
abcCAV4C5E,abckkkCC7F,abchum002d,abckkk795e
98xxyy31A4,abckkkA20D,abcmsy427e,abcs402bef
abcBTPD088,abckkk9940,abcmsy63df,abcbtp2535
86SOPSF17F,abckkk92D1,abcmsyc1a5,abcs10b660
abcFSEBACF,abckkk795E,abcmed66d4,110car1578
abcMORC6C3,abckkk6DB3,abcmed7e63,abckkk3085
abcEWOC216,abckkk6AE1,abcmed94a1,abcfao4c5e
35Sbvc9A41,abckkk47BD,abcpaodc2d,abcbtpcd19
110car8553,abckkk4705,abcrad8991,abcs50271a
abcMOT6C09,abckkk3085,abcs100659,abcs50fea4
abcCAV2D26,abcDISD98B,abcs10266f,abccave1cb
015ooo7D7B,abcqweFC9A,abcs102b9d,abcmed7e63
326QMSBC84,abcqweD088,abcs1035fc,abcqwe761c
abcCAVC216,abcqweCC42,abcs10a362,abcjewa249
abcTOC6887,abcqweCB6F,abcs10b660,abcjewac64
110car5F08,abcqweC8F8,abcs10c6dd,abcjewa093
546MOT3704,abcqweA289,abcs202886,abcjew2d0a
abcBTP5B76,abcqweA01B,abcs20849d,98xxyy1335
abcBTP0146,abcqwe761C,abcs20c632,98xxyy3341
abcS309681,abcqwe4CEC,abcs3031c3,635mmm4e0f
abcBTPE02A,abcCSM4DAC,abcs307e84,98xxyy683b
abcHHT63DF,abcCHPB336,abcs30810f,abcqwe4cec
abcHHTE1A5,abcCAV19B1,abcs30a197,35sbvcafc7
abcHHTC423,abcBTPE559,abcs30b691,abcs50dbf6
abcHHT4C8A,abcBTPE158,abcs402bef,abckkk4705
abcXO02710,abcBTPC47C,abcs404b93,abckkkd0ef
86SbvcF17F,abcBTPA02A,abcs407f45,105qmd54a4
oyyIGX431D,abcBTP9F78,abcs40c36b,abcbtpe158
abcS302673,abcBTP75BA,abcs50271a,2564xy4ac9
abcTOC7E84,abcBTP4A8D,abcs50b28d,abcs404b93
abcTOCB602,abcBTP2535,abcs50dbf6,700mmm1685
abcTOC1D98,abcBTP18BD,abcs50eba2,abcs407f45
98xxyy7B8C,abcjewE770,abcs50f02f,abcs3031c3
PRTTOC7559,abcjewAC64,abcs50fea4,abcs30a197
PRTmsyOR80,abcjewA2E9,abcs6041bc,abcqwed088
PRTabcD635,abcjewA249,abcs609413,abcmed94a1
PRTTOCAAB3,abcjewA093,abcs60a3f0,98xxyy9f83
PRToooA671,abcjew4FB3,abcs60c727,abcs50b28d
abcSUPE33A,abcjew2D0A,abcs6ob28c,abcbtpc47c
abcmsy427E,35SbvcF98F,abghgo2519,abcs40c36b
abckkk6AE1,35Sbvc9BEF,abghgob60d,medops6ee0
abcqweA289,35Sbvc615F,abcsupe33a,834posdd6a
abcmsyC1A5,700mmmE6DE,abctoc2533,abcqwec8f8
abcPAODC2D,700mmmE6C5,abctocec5d,abcqwecb6f
abcMED7E63,105QMD54A4,abcuavb5da,abcpaodc2d
abckkk3085,105QMD02AC,abcxo02c70,abckkk47bd
abckkkA20D,2564xyA559,466denb3f7,abcs30b691
abckkkB4E8,2564xy4AC9,546mat027f,dcma001999
abcCAVC426,2564xy1E36,546mot4030,abcjewe770
abcBTPE158,,635mmm1e37,35sbvc9bef
abckkk47BD,,635mmm4e0f,abcs1035fc
abcqweA01B,,110car1578,oyytcfc55c
abcBTP9F78,,110unc179e,abckkkeffc
abcHUM002D,,732ghg242a,abcs307e84
abcDISD98B,,732ghg24a0,2564xya559
abcmsyC613,,732ghga02d,abcbtpe559
abcS407F45,,834pos3386,abckkkd08b
abcS6OB28C,,834posdd6a,abcqwefc9a
abcRAD8991,,98xxyy1335,abcmsy427e
abcFAO4C5E,,98xxyy27b5,abcbtp9f78
abcS50EBA2,,98xxyy27c9,abcdisd98b
abcS609413,,98xxyy3341,105qmd02ac
abcS60C727,,98xxyy683b,abctocec5d
abcS6022D2,,98xxyy8113,abcjew38e2
DSabcP214F,,98xxyy8258,naveod20ba
abcjewA093,,98xxyy9f83,nnastyuin9991
abcS60B2BC,,98xxyy9fcf,
abcjewA2E9,,dcma001999,
abcqweCC42,,oyyospa6dd,
abcjewAC64,,oyytcfc55c,
abcjewA249,,medops6ee0,
abcjew2D0A,,naveod20ba,
abcEWOC492,,ooo-1dcca8,
abcS404B93,,ooo-1def1e,
abcqwe4CEC,,ooo-c5821a,
abcS20849D,,nnasexeizn001,
abcjew4FB3,,nnastyuin9991,
abcHHT436E,,nnastyuin9992,
abcBTPA02A,,nnastyuin9993,
abcCHPB336,,nnasiaizn9991
abcCAVE1CB,,nnasiaws9991
abcUAVB5DA,,
abcCSM4DAC,,
abcBTP75BA,,
abcqweC8F8,,
abcBTP2535,,
abcjewE770,,
abcMED94A1,,
abckkk4705,,
abckkkD0EF,,
abcqwe761C,,
abcqweFC9A,,
abcBTP18BD,,
abckkkE449,,
abcS6041BC,,
abcCAV19B1
abcS100659
abckkk9940
abcS20C632
abcBTPE559
abcHUM3754
abcjew38E2
abckkk92D1
abcHHTB52E
abckkkF3C4
abcS30810F
abckkk795E
abckkkE360
abcJNNC216
abckkkE7D8
abcS60A3F0
abcMED66D4
abcMORC6CE
abcqweCB6F
abcBTP4A8D
abckkkCC7F
abcBTPCD19
abcBTPC47C
abcS402BEF
abcS40C36B
abckkk6DB3
abcXO02C70
abcS50F02F
abcS50B28D
abcS50271A
abcqweD088
abcS50DBF6
abckkkEFFC
abcS30B691
abcS30A197
abcS307E84
abcTOCEC5D
abcTOC2533
abcS3031C3
abcS20C4B1
abckkkD08B
abcS202886
abghgOB60D
abghgO2519
abcS10C6DD
abcS102B9D
abcS10B660
abcS1035FC
abcS50FEA4
abcS10A362
abcS10266F
abcmsy63DF
abcjewF008
86Sbvc18D9

Open in new window

0
 
LVL 10

Accepted Solution

by:
mikeopolo earned 2000 total points
ID: 20390439
This should be possible using a pivot table.

Reorganise the data first - add a column A, and give it the value of AD in all rows. THen copy column C to under column B, and put the label SAV in all rows.

Ditto column D (WSUS) and column E (NET).
Then delete columns C-E
Then add labels in column A and B - eg "Tool", "Computer"
Then select any cell, and choose Data, Pivot Table.
Choose Next, Next
Choose Layout.
For the Row field, select computer name
For the Column field, select Tool.
For the data field, select Count of Computername.

Double click the Computer name field (in the Row...) and choose "no subtotal", then Advanced. Then sort, and select computer name to sort by.

Then OK and OK and OK, and Finish.

The tools may appear vertically - drag the label for that column out to the right and release.

hth
Mike
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:origfla
ID: 20390579
Just wanted to say your solution is KICK A$$ - nice thinking!
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20390642
I wonder how long that takes to do for EACH time an audit is run...
0
 
LVL 10

Expert Comment

by:mikeopolo
ID: 20390650
origfla...thanks!

imitchie...From the time I pasted the sample data in to a worksheet it took about 90 seconds to prepare the pivot table - everyone has their own ways of working so I can't guarantee that, but provided the data volumes are not huge it's a fairly minor task.

Regards
Mike
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20390696
I'm just thinking practically, as an admin, I don't want to spend 3 minutes doing this every week. I would have used a macro that includes in the last step a filter for only the mismatches. But like you said, to each his own
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question