Excel Question 1

[tberte]

I have four columns of values, as shown here:

413 413 413 413
437 437 437 437
486 486 486 486
493 493 493 493
547 547 547 547
566 610 610 579
579 616 667 601
610 667 668 610
616 668 708 667
668 708 733 668
719 730 782 676
730 733 782 708
733 782 802 733
782 786 822 779
800 802 878 782
802 822 902 802
822 878 924 822
878 902 933 878
897 905 947 902


I need all of the numbers that exist in all four columns to lined up.

Example:

The first line is 413 413 413 413

Now scroll down to 782. If you look, 782 exists in all four columns, but is in completely different rows. I need the numbers that exist in all four rows to be lined up. If they don't exist in all rows, they could just be lined up with blank column.

 

[jdhilljr]

I would use a vlookup.

Insert 4 columns so that data is in e through h

A1 =SUM(E1)
B1 =VLOOKUP(A1,$F$1:$F$19,1,0)
C1 =VLOOKUP(A1,$G$1:$G$19,1,0)
D1 =VLOOKUP(A1,$H$1:$H$19,1,0)

#N/A will show if not found

Just copy the formulas down after the initial one. Be sure to adjust for your data.

Jim

 

[KenWright]

Little bit of monkeying about but it does what you want:-

Insert Column to left of data and put 'a' alongside all your data.

Now move 2nd column below 1st and put b alongside
Now move 3rd column below 2nd and put c alongside
Now move 4th column below 3rd and put d alongside

Data will now look like this

a 101
a 102
a 123
a 125
b 101
b 103
b 104
b 105
c 101
c 102
c 103
c 104
d 101
d 102
d 105
d 106

Label 1st column abcd (ie put header above data)
Label 2nd column Value

Select all data, do Data / Pivot table and Pivot Chart Report, hit Next / next / Finish

Drag abcd into Column fields
Drag Value into Row fields
Drag Value into Data field ALSO

Done.

Regards
Ken.................




----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[jdhilljr]

Use these if you have to have blank cells.
B1 =IF(ISNA(VLOOKUP(A1,$F$1:$F$19,1,0)),"",A1)
C1 =IF(ISNA(VLOOKUP(A1,$G$1:$G$19,1,0)),"",A1)
D1 =IF(ISNA(VLOOKUP(A1,$H$1:$H$19,1,0)),"",A1)

Jim

 

[Zathras]

Since this is not the VBA forum, I assume you want a non-VBA solution.

1. I put your data in A220 with a header row in A11 of "a", "b", "c" and "d"

2. I then copied your data into "F2:F77", one column at a time and labeled the data "x" in F1.

3. I then used Data/Filter/Advanced Filter... and copied unique rows from F1:F77 into Cell H1. This created unique numbers in H2:H31.

4. Then I put the following formulas in I2
[tt]
=IF(ISNA(MATCH($H2,A$2:A$20,0)),"",$H2)
[/tt]
5. Finally, I copied the formula from I2 into I2:L31 where I can see the result.

6. The last step would be to copy and paste/special values from I2:L31 into wherever you need the finished table:
[tt]
413 413 413 413
437 437 437 437
486 486 486 486
493 493 493 493
547 547 547 547
566
579 579
601
610 610 610 610
616 616
667 667 667
668 668 668 668
676
708 708 708
719
730 730
733 733 733 733
779
782 782 782 782
786
800
802 802 802 802
822 822 822 822
878 878 878 878
897
902 902 902
905
924
933
947
[/tt]
Jim, I don't see where your method would result in a final table of 30 rows.

 

[jdhilljr]

The one thing I missed. You are correct. Mine only grabs from the front.
Ooooop's

Jim

 

[byundt]

If your data is in A119, then you can use two array formulas to return the rearranged data in cells E1:Hxx.

In cells E1:H1, enter the following array formula:
=IF(ISERROR(MATCH(MIN($A$1:$D$19),A$1:A$19,0)),"",MIN($A$1:$D$19))

Then in cells E2:H2, enter the following array formula:
=IF(ISERROR(MATCH(MIN(IF($A$1:$D$19>MAX($E$1:$H1),$A$1:$D$19,"")),A$1:A$19,0)),"",MIN(IF($A$1:$D$19>MAX($E$1:$H1),$A$1:$D$19,"")))

Both of these are array formulas, so remember to hold the Control + Shift down while pressing Enter. Excel should respond by adding curly braces surrounding the formula.

Both formulas may be copied across and down, and will return either the value or a blank if that value is not in the corresponding column. Copy them down until they return all blanks in that row.

 

[byundt]

I forgot to mention that the formula in cells E2:H2 should be copied down until it returns all blanks in the row.

 

[tberte]

Thank Byundt...I think your's was the easiest to use and it worked flawlessly. I have several more cells of data, so this was just the beginning. Thanks to all of you guys though!