Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check columns for data match...
Using XL 2000
I have a worksheet which contains 10 columns A:J and over 500 lines. The data in each cell in the Column range E:J contains either a TRUE or FALSE. Row 1 contains header labels for each column. Im looking for a routine to loop through each column, per line and where a TRUE exists in that column, and ( where more than one TRUE is found) concatenate the label name in to column K of the same row being checked. Hope this makes sense. Ideas appreciated. Thanks, Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check columns for data match...
You can do this as a worksheet formula instead of code; below is the formula
for K2, which could then be copied down for each row in your list: =IF(E2,E$1,"")&IF(F2,F$1,"")&IF(G2,G$1,"")&IF(H2,H $1,"")&IF(I2,I$1,"")&IF(J2,J$1,"") If you want spaces between the label names: =TRIM(IF(E2,E$1&" ","")&IF(F2,F$1&" ","")&IF(G2,G$1&" ","")&IF(H2,H$1&" ","")&IF(I2,I$1&" ","")&IF(J2,J$1&" ","")) "Paulc" wrote: Using XL 2000 I have a worksheet which contains 10 columns A:J and over 500 lines. The data in each cell in the Column range E:J contains either a TRUE or FALSE. Row 1 contains header labels for each column. Im looking for a routine to loop through each column, per line and where a TRUE exists in that column, and ( where more than one TRUE is found) concatenate the label name in to column K of the same row being checked. Hope this makes sense. Ideas appreciated. Thanks, Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check columns for data match...
How about a formula:
=IF(E2,$E$1,"")&IF(F2,$F$1,"")&IF(G2,$G$1,"")&IF(H 2,$H$1,"") &IF(I2,$I$1,"")&IF(J2,$J$1,"") (all one cell) or if you want spaces between values: =TRIM(IF(E2,$E$1,"")&" "&IF(F2,$F$1,"")&" "&IF(G2,$G$1,"")&" "&IF(H2,$H$1,"") &" "&IF(I2,$I$1,"")&" "&IF(J2,$J$1,"")) (still all one cell) Paulc wrote: Using XL 2000 I have a worksheet which contains 10 columns A:J and over 500 lines. The data in each cell in the Column range E:J contains either a TRUE or FALSE. Row 1 contains header labels for each column. Im looking for a routine to loop through each column, per line and where a TRUE exists in that column, and ( where more than one TRUE is found) concatenate the label name in to column K of the same row being checked. Hope this makes sense. Ideas appreciated. Thanks, Paul -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check columns for data match...
Thanks for your ideas.
looking at my options again, I need to resolve this programatically. I think my answer is based around a column loop through routine. Just not sure of the code. Paul "Dave Peterson" wrote: How about a formula: =IF(E2,$E$1,"")&IF(F2,$F$1,"")&IF(G2,$G$1,"")&IF(H 2,$H$1,"") &IF(I2,$I$1,"")&IF(J2,$J$1,"") (all one cell) or if you want spaces between values: =TRIM(IF(E2,$E$1,"")&" "&IF(F2,$F$1,"")&" "&IF(G2,$G$1,"")&" "&IF(H2,$H$1,"") &" "&IF(I2,$I$1,"")&" "&IF(J2,$J$1,"")) (still all one cell) Paulc wrote: Using XL 2000 I have a worksheet which contains 10 columns A:J and over 500 lines. The data in each cell in the Column range E:J contains either a TRUE or FALSE. Row 1 contains header labels for each column. I€„¢m looking for a routine to loop through each column, per line and where a TRUE exists in that column, and ( where more than one TRUE is found) concatenate the label name in to column K of the same row being checked. Hope this makes sense. Ideas appreciated. Thanks, Paul -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check columns for data match...
If it needs to be done programatically, then you could do it like this:
Dim ListLength as Integer, RowCounter as Integer, CellVal as String ListLength = Range("A1").CurrentRegion.Rows.Count For RowCounter = 2 to ListLength With Range("K" & RowCounter) .Formula = "=IF(E2,$E$1,"""") & IF(F2,$F$1,"""") & IF(G2,$G$1,"""") & IF(H2,$H$1,"""") & IF(I2,$I$1,"""") & IF(J2,$J$1,"""")" CellVal = .Value .Value = CellVal End With Next RowCounter "Paulc" wrote: Thanks for your ideas. looking at my options again, I need to resolve this programatically. I think my answer is based around a column loop through routine. Just not sure of the code. Paul "Dave Peterson" wrote: How about a formula: =IF(E2,$E$1,"")&IF(F2,$F$1,"")&IF(G2,$G$1,"")&IF(H 2,$H$1,"") &IF(I2,$I$1,"")&IF(J2,$J$1,"") (all one cell) or if you want spaces between values: =TRIM(IF(E2,$E$1,"")&" "&IF(F2,$F$1,"")&" "&IF(G2,$G$1,"")&" "&IF(H2,$H$1,"") &" "&IF(I2,$I$1,"")&" "&IF(J2,$J$1,"")) (still all one cell) Paulc wrote: Using XL 2000 I have a worksheet which contains 10 columns A:J and over 500 lines. The data in each cell in the Column range E:J contains either a TRUE or FALSE. Row 1 contains header labels for each column. I€„¢m looking for a routine to loop through each column, per line and where a TRUE exists in that column, and ( where more than one TRUE is found) concatenate the label name in to column K of the same row being checked. Hope this makes sense. Ideas appreciated. Thanks, Paul -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question regarding VLookup and if it can check two columns of data | Excel Worksheet Functions | |||
Match Data for 4 Columns | Excel Discussion (Misc queries) | |||
Match data in 2 columns and return data from 3rd column | Excel Worksheet Functions | |||
match columns and associated data | Excel Discussion (Misc queries) | |||
MATCH UP DATA IN COLUMNS | Excel Worksheet Functions |