Sorting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The file has two worksheets.

Sheet one will be sorted and Sheet two will remain static.

Column F in Sheet two is reading the values from Sheet one in column F (you
can see the forumula in column F sheet 2) (For Ex: =SUM(Sheet1!F2)

For example, if I sort sheet one (any column) then Sheet 2 does not remain
static and the integrity of the data is lost. What happens is Sheet 2 column
F will change but the other columns in sheet 2 don't change and the integrity
of the data is lost.

If you like I can email you the file.

Hope that makes sense.

Please let me know how to resolve this.

Thanks
cmckeag
 
The file has two worksheets.
Sheet one will be sorted and Sheet two will remain static.

Column F in Sheet two is reading the values from Sheet one in column F
(you can see the forumula in column F sheet 2) (For Ex:
=SUM(Sheet1!F2)
Click to expand...

As an aside, "SUM" is doing nothing here since there's only one number in
the sum. I'd suggest simply =Sheet1!F2 for clarity.

For example, if I sort sheet one (any column) then Sheet 2 does not
remain static and the integrity of the data is lost. What happens is
Sheet 2 column F will change but the other columns in sheet 2 don't
change and the integrity of the data is lost.
Click to expand...

It's often the case that there's a "key" column that identifies the row
uniquely; for example, an account number or a part ID or a date or
something like that.

If your spreadsheet has a column like that and it appears in both sheets,
then read about the VLOOKUP function in Excel's built-in Help. Used in
Sheet2!F2, it'll find the value in Sheet1 column-F that has the same key
value as the current row.

For example, if the key column is A in Sheet1 and B in Sheet2, try the
following in F2 and copy down:
=VLOOKUP(B2,Sheet1!A:F,6,FALSE)

Modify to suit.
 
Thank you for your help but your response did not resolve the issue. I
copied the spread sheet below and for simplicity I made the data very simple.

Sheet one column F is doing a sum



SHEET 1


A B C D E F

A A A A A 1
B B B B B 2
C C C C C 3
D D D D D 4
E E E E E 5
F F F F F 6


SHEET 2

A B C D E F

A A A A A 1
B B B B B 2
C C C C C 3
D D D D D 4
E E E E E 5
F F F F F 6

As an aside, "SUM" is doing nothing here since there's only one number in
the sum. I'd suggest simply =Sheet1!F2 for clarity
Click to expand...

I don't think this matters but Sheet one is a sum of values reading over to
sheet 2 but in my example I left that out just to make it simple.

Back to the question. I read about Vlookup, however, I do not want to do a
lookup I want to sort spreadsheet one and the integrity of the data to stay
the same in sheet 2.

I can email you the file if this would help.

Thank you again!
 
I included this additional information to see if this will help

Before Sort:

SHEET 1


A B C D E F

A A A A A 1
B B B B B 2
C C C C C 3
D D D D D 4
E E E E E 5
F F F F F 6


SHEET 2

A B C D E F

A A A A A 1
B B B B B 2
C C C C C 3
D D D D D 4
E E E E E 5
F F F F F 6


After Sort: In this instance I sorted Sheet 1 column A. Notice how the
data integrity is lost in Sheet 2

Sheet 1
A B C D E F

F F F F F 6
E E E E E 5
D D D D D 4
C C C C C 3
B B B B B 2
A A A A A 1


Sheet 2

Notice how column F changed and the following columns lost integrity

A B C D E F

A A A A A 6
B B B B B 5
C C C C C 4
D D D D D 3
E E E E E 2
F F F F F 1

I incuded this example for further clarification.

Again. I can email you the file if you would like.

Thank you for your assistance!
 
*******************************

MyVeryOwnSelf said:
As an aside, "SUM" is doing nothing here since there's only one number in
the sum. I'd suggest simply =Sheet1!F2 for clarity.



It's often the case that there's a "key" column that identifies the row
uniquely; for example, an account number or a part ID or a date or
something like that.

If your spreadsheet has a column like that and it appears in both sheets,
then read about the VLOOKUP function in Excel's built-in Help. Used in
Sheet2!F2, it'll find the value in Sheet1 column-F that has the same key
value as the current row.

For example, if the key column is A in Sheet1 and B in Sheet2, try the
following in F2 and copy down:
=VLOOKUP(B2,Sheet1!A:F,6,FALSE)

Modify to suit.
Click to expand...
 
Before Sort:
SHEET 1


A B C D E
F

A A A A A 1
B B B B B 2
C C C C C 3
D D D D D 4
E E E E E 5
F F F F F 6


SHEET 2

A B C D E
F

A A A A A 1
B B B B B 2
C C C C C 3
D D D D D 4
E E E E E 5
F F F F F 6


After Sort: In this instance I sorted Sheet 1 column A. Notice how
the data integrity is lost in Sheet 2

Sheet 1
A B C D E
F

F F F F F 6
E E E E E 5
D D D D D 4
C C C C C 3
B B B B B 2
A A A A A 1


Sheet 2

Notice how column F changed and the following columns lost integrity

A B C D E
F

A A A A A 6
B B B B B 5
C C C C C 4
D D D D D 3
E E E E E 2
F F F F F 1
Click to expand...


I guess I don't understand what you mean by "data integrity." Please
explain.

I though integrity meant that the letter in column A (say) determines which
row matters in column F. For example, I thought you wanted Sheet2!F1 to be
1 because Sheet2!A1 = "A" and Sheet1!A6 = "A" and Sheet1!F6 = 1. If so,
vlookup can help.

If instead you want to freeze the Sheet2 column C values, you can copy the
column before the sort and use
Edit > Paste special > Values
to overwrite the formulas with constant values.
 
What I mean by data integrity lost is when you sort on Sheet 1 (any column),
Sheet 2 Column F changes but the other columns in Sheet 2 do not follow suite.

Further clarification:


"After Sort" SHEET1 ROW 1 = F and then column F= 6.

In Sheet 2 row A should always be Number 1 in column F. However that is
not the case. Row F Changed in Sheet 2 but the (OTHER COLUMNS DID NOT
CHANGE.)

I wanted to make the data simple so you can see what I am explaining.

Hopefully this clarification is better. If not please let me know.

Thank you again for your assistance!
 
Hopefully this clarification is better. If not please let me know.

Please post before & after showing (a) the result you get and (b) the
result you want.

Don’t use the same value (like "A") in more than one place unless those
values are in fact identical.
 
(a) the result you get

Sheet 1 (result of sort)

F F F F F 6
E E E E E 5
D D D D D 4
C C C C C 3
B B B B B 2
A A A A A 1


Sheet 2 (results of sort)

A A A A A 6
B B B B B 5
C C C C C 4
D D D D D 3
E E E E E 2
F F F F F 1



(b) the result you want

Sheet 2 should look like this:

F F F F F 6
E E E E E 5
D D D D D 4
C C C C C 3
B B B B B 2
A A A A A 1

Thank you again for looking at this. If this does not clear it up for you
can I email you the file?
 
Thank you for your help, I got the problem resolved.

I used the index function and the match function to resolve this.

Thanks!
 
Back
Top