comparison - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

comparison

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2005-06-23 : 11:08:41

This all may be a little over my head, but I'll throw it out there and see if anyone can help.

I have a need to do what I guess you would call a comparision on two columns in an excel spreadsheet and move the rows around to match up any duplicates. in other words, if 'Orlando' is found in cell A20 AND in B21, B35 and B40, move those rows so that all of the 'Orlando's end up in a grouping.

Is there a way to maybe there was a way to dump the excel into a SQL Server table, massage the data to match up the rows and then dump it back out to a spreadsheet (while preserving the rest of the data in the spreadsheet)???

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-23 : 11:12:50

Got any keys to the data?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2005-06-23 : 11:19:41

I imported the one sheet that I am working with from the spreadsheet into a table in SQL Server, but I have no Key established.

Kristen
Test

22859 Posts

Posted - 2005-06-23 : 12:03:31

Can you just do a SORT in Excel?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-23 : 12:16:11

i wouldn't... it sorts only on a column basis not on a row basis.

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-23 : 12:17:44

you could do this comparison in .net, no?
excel .net wrapper supports the column comparison.
well i worked with OWC but it's probably the same thing.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-06-24 : 01:23:23

"i wouldn't... it sorts only on a column basis not on a row basis"

You sure? That's not been myt experience - but maye I don't understand the problem

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-24 : 05:57:53

screwed my self over with that a few times... but then again i may have been doing it wrong...

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-24 : 06:12:33

quote:
i wouldn't... it sorts only on a column basis not on a row basis.

Select all columns then sort
It will sort now rowwise

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-06-24 : 07:10:17

Or select no columns (i.e. a the current, single, cell will be highlighted).

For this to work I think there should be NO blank cells in Row 1 (assuming that Row 1 has "Column Labels"), and there is a chance that blank rows cause the "extent" of the sort area to be truncated.

But a table full of data should work fine.

I normally insert a new column 1, 2, 3, ... so that I can sort back to "original order" if needed.

Kristen

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2005-06-24 : 09:51:56

I'm sure I didn't explain myself at the beginning of the post..

four columns of data in a spreadsheet - Columns A B C and D
data in columns A and B are related
data in column C and D are related and must stay together in the same row.

I need to do a comparison on the data in rows B and C and if there is a match, move the data around to group all the 'matches' together.

does this sound do-able?

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-24 : 11:00:46

It would really help if you posted some sample data...or following the link below....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2005-06-24 : 11:37:36

Sample data below. four columns of data in a spreadsheet - Columns A B C and D
data in columns A and B are related
data in column C and D are related and must stay together in the same row.

I need to do a comparison on the data in rows B and C and if there is a match, move the data around to group all the 'matches' together.

does this sound do-able?
MSO NAME PRIMARY SERVICE AREA Service Area System Name
ABC Comm Cullman Cullman (D) ABC/Cullman, AL
ABC Comm Enterprise Enterprise (D) ABC/Enterprise, AL
ABC Comm Fort Payne Fort Payne (D) ABC/Fort Payne, AL
ABC Comm Yuma Warrior (D) ABC/Warrior, AL
ABC Comm Anaheim Yuma (D) ABC/Yuma, AZ

Kristen
Test

22859 Posts

Posted - 2005-06-24 : 12:57:20

Can't you jsut sort by columns A & B - thus if A and B are equal, all other rows with the same values for A and B will be sorted to be on adjacent rows.

If you want all A = B rows first, then all A <> B rows insert a new column

=IF(An = Bn, 1, 0)

where "n" is the current row number, and sort by that new column!

Kristen

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2005-06-24 : 13:58:50

Thanks for all your suggestions.. my 'sample data' I think only confused things. I do need to sort the data - matching up the data in two different columns and moving them so they are all grouped together - but I need to do this without messing up the row data. I thought there was maybe a way to do it by dumping the excel into a sql servcer table, manipulating the data there and then dumping it back out.. I'll have to keep trying :)

Kristen
Test

22859 Posts

Posted - 2005-06-24 : 14:16:12

In what way does it mess up the row data? This is the bit I don't think I'm understanding

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources