Solved

How sort and match multiple Excel rows to one row

Posted on 2007-12-01
8
1,729 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
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 4

Expert Comment

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

Expert Comment

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

Expert Comment

by:mikeopolo
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now