Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old January 3rd 06, 05:23 AM posted to microsoft.public.excel.worksheet.functions
B. R.Ramachandran
 
Posts: n/a
Default Matrix to single column

Hi RD,

Thanks for your feedback. I believe that the following modification could
work.
In the cell corresponding to Row 1 of the 4th column to be created (i.e.,
J1, in my previous reply), use the following formula

=ROW()+IF(OR(G1="",G1="delete",I1=0),100000,0)

Note that the formula references columns G and I (1st and the 3rd of the new
columns). If one or more cells in Column A (and, hence, in Column G) contain
"", the formula will create numbers greater than 100000 in the corresponding
rows. When sorted, those rows will clump up at the bottom which are
eventually deleted (thus, even though the COUNTA function does not ignore
""s, it doesn't hurt).

Regards,
B. R. Ramachandran





"RD Wirr" wrote:

Hello B.R.
Thanks for the suggestion. This works well but for one problem. My Matrix is
a big table of lookups and references to other cells. Most of them are
calculating to a value of "". The COUNTA function finds all those "" but
still non-blank cells and I get the full listing of cells with empty data
sets. I have been searching for a way to COUNTIF($A$2:$A$6,<"") or
COUNTIF($A$2:$A$6,"<""") but I have had no luck with this. Do you know how
to count cells that are and actaul text value (i.e. TD 01360) but not cells
that have a formula equaling ""?
Regards,
RDW

"B. R.Ramachandran" wrote:

Hi,

Another approach using formulas:

Let's us suppose that your matrix is in columns A thru E (for your sample
data, it is A1:E6). Create 4 new columns (say, G, H, I, and J) with the
following formulas in Row 1 (i.e., G1, ...J1); of course, modify the column A
range in the formulas (here showing as "$A$2:$A$6") to suit to your data.

=IF(ROW()4*COUNTA($A$2:$A$6),"delete",OFFSET($A$2 ,MOD(ROW()-1,COUNTA($A$2:$A$6)),0))

=OFFSET($B$1,0,QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))

=OFFSET($B$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))

=ROW()+IF(OFFSET($B$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))=0,100000,0)

Autofill the formulas down the rows generously. Delete the rows at the
bottom where column G shows "delete".

These columns are still linked to the original matrix. To make them
independent, select and copy the entire area of the new columns, "Edit" --
"Paste Special" --"Values" -- "OK".

Sort the new columns by Column J ascending, and delete the rows at the
bottom where Column J shows numbers greater than 100000).

Delete the last column (Column J)

Regards,
B. R. Ramachandran


"RD Wirr" wrote:

Just to illustrate what I am talking about here, data looks like this:

Upper Lower Inner Outer
TD 01360 1 1 0 1
TD 01373 2 2 0 1
TD 01361 0 2 0 1
TD 01364 0 2 0 1
TD 01378 3 0 1 0

and I am trying to work out a formula that will transpose it into a filtered
(0) column that looks like this:

TD 01360 Upper 1
TD 01373 Upper 2
TD 01378 Upper 3
TD 01360 Lower 1
TD 01373 Lower 2
TD 01361 Lower 2
TD 01364 Lower 2
TD 01378 Inner 1
TD 01360 Outer 1
TD 01373 Outer 1
TD 01361 Outer 1
TD 01364 Outer 1

Thanks in advance,
RDW


  #12   Report Post  
Old January 3rd 06, 06:41 AM posted to microsoft.public.excel.worksheet.functions
RD Wirr
 
Posts: n/a
Default Matrix to single column

Thanks B.R.
That works. It makes a pretty big data set (I have around 1000 rows x 7
columns) but it is managable. Actually I don't copy/paste/sort and delete to
clean the data. I use a VLookup to extract the useful data on the fly. The
original data changes constantly and some other people have to use this sheet
so I don't want them to have to do the manual procedure.
Thanks again and Regards,
RDW


"B. R.Ramachandran" wrote:

Hi RD,

Thanks for your feedback. I believe that the following modification could
work.
In the cell corresponding to Row 1 of the 4th column to be created (i.e.,
J1, in my previous reply), use the following formula

=ROW()+IF(OR(G1="",G1="delete",I1=0),100000,0)

Note that the formula references columns G and I (1st and the 3rd of the new
columns). If one or more cells in Column A (and, hence, in Column G) contain
"", the formula will create numbers greater than 100000 in the corresponding
rows. When sorted, those rows will clump up at the bottom which are
eventually deleted (thus, even though the COUNTA function does not ignore
""s, it doesn't hurt).

Regards,
B. R. Ramachandran





"RD Wirr" wrote:

Hello B.R.
Thanks for the suggestion. This works well but for one problem. My Matrix is
a big table of lookups and references to other cells. Most of them are
calculating to a value of "". The COUNTA function finds all those "" but
still non-blank cells and I get the full listing of cells with empty data
sets. I have been searching for a way to COUNTIF($A$2:$A$6,<"") or
COUNTIF($A$2:$A$6,"<""") but I have had no luck with this. Do you know how
to count cells that are and actaul text value (i.e. TD 01360) but not cells
that have a formula equaling ""?
Regards,
RDW

"B. R.Ramachandran" wrote:

Hi,

Another approach using formulas:

Let's us suppose that your matrix is in columns A thru E (for your sample
data, it is A1:E6). Create 4 new columns (say, G, H, I, and J) with the
following formulas in Row 1 (i.e., G1, ...J1); of course, modify the column A
range in the formulas (here showing as "$A$2:$A$6") to suit to your data.

=IF(ROW()4*COUNTA($A$2:$A$6),"delete",OFFSET($A$2 ,MOD(ROW()-1,COUNTA($A$2:$A$6)),0))

=OFFSET($B$1,0,QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))

=OFFSET($B$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))

=ROW()+IF(OFFSET($B$2,MOD(ROW()-1,COUNTA($A$2:$A$6)),QUOTIENT(ROW()-1,COUNTA($A$2:$A$6)))=0,100000,0)

Autofill the formulas down the rows generously. Delete the rows at the
bottom where column G shows "delete".

These columns are still linked to the original matrix. To make them
independent, select and copy the entire area of the new columns, "Edit" --
"Paste Special" --"Values" -- "OK".

Sort the new columns by Column J ascending, and delete the rows at the
bottom where Column J shows numbers greater than 100000).

Delete the last column (Column J)

Regards,
B. R. Ramachandran


"RD Wirr" wrote:

Just to illustrate what I am talking about here, data looks like this:

Upper Lower Inner Outer
TD 01360 1 1 0 1
TD 01373 2 2 0 1
TD 01361 0 2 0 1
TD 01364 0 2 0 1
TD 01378 3 0 1 0

and I am trying to work out a formula that will transpose it into a filtered
(0) column that looks like this:

TD 01360 Upper 1
TD 01373 Upper 2
TD 01378 Upper 3
TD 01360 Lower 1
TD 01373 Lower 2
TD 01361 Lower 2
TD 01364 Lower 2
TD 01378 Inner 1
TD 01360 Outer 1
TD 01373 Outer 1
TD 01361 Outer 1
TD 01364 Outer 1

Thanks in advance,
RDW

  #13   Report Post  
Old January 3rd 06, 06:02 PM posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
 
Posts: n/a
Default Matrix to single column

For Qty use this formula instead
=INDEX(array1,SUMPRODUCT((array_n=set1 R)*set_r),
SUMPRODUCT((array_n=set1 R)*set_c))

  #14   Report Post  
Old January 4th 06, 09:06 PM posted to microsoft.public.excel.worksheet.functions
RD Wirr
 
Posts: n/a
Default Matrix to single column

You are a wizard Herbert. Thanks very much that does it. I'd like to find out
where a person learns about these things. Especially "array_n=set1 R".
Thanks again,
RDW

"Herbert Seidenberg" wrote:

For Qty use this formula instead
=INDEX(array1,SUMPRODUCT((array_n=set1 R)*set_r),
SUMPRODUCT((array_n=set1 R)*set_c))




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return Range of Numerical Values in Single Column based on Frequency Percentage Sam via OfficeKB.com Excel Worksheet Functions 9 October 28th 05 11:01 PM
Changing single column default to Upper case Graham Excel Discussion (Misc queries) 4 September 27th 05 05:17 PM
Return Count for LAST NonBlank Cell in each Row Sam via OfficeKB.com Excel Worksheet Functions 12 April 17th 05 10:36 PM
Change the width of a single column in a column chart Dave Charts and Charting in Excel 2 December 13th 04 07:25 PM
How can I sort an entire spreadsheet from a list prod sorter Excel Worksheet Functions 4 November 17th 04 03:43 AM


All times are GMT +1. The time now is 01:08 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017