Retrieve only non-blank cells from a lookup

[scooterny]

Folks,

I have an Excel 2003 question. I have a particular column filled with character information, but not every cell in the column has data in it. I want to retrieve only the non-blank information from this column into another column. So for instance, I am retrieving data from column C and C1, C4, C9, and C19 all have valid text data in them. I want Column D, rows 1 - 4 to contain the values in C1, C4, C9, and C19, and so forth. I believe this solution probably requires a lookup function along with and isblank function ( or something like this ), but my solutions are not coming along as well as expected and I don't have as much time as I would like to play with this. Can anyone please suggest a solution for this? Thanks so much.

Scott

 

[SkipVought]

Hi,

It is not clear what you are looking up and what you are returning.

Please post a representative sample and clear explanation.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[scooterny]

Skip,

Thanks for responding so quickly. I will try to explain what I want more clearly. I imagined some kind of lookup, but upon second thought, that probably is not the best way to go. I only have one column of data, so there really is no way to look anything up. I am including a sample spreadsheet of what I am up against. I have a column, in this sample spreadsheet I have used column A to hold my data. I have data every few rows, but not in every row in that column. I want to grab all of the non-blank cells in that column and put that information in column C, as I have manually done in this example. It doesn't seem that complicated to me, but I am not exactly sure how to do it. Thanks again.

Scott

 

[SkipVought]

Your c:\ drive is not on the internet!

Plesae post a representative sample of the data you are working with: good ol' copy 'n' paste.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[scooterny]

Skip,

Sorry about that. Here is an example of what I would like.

Scott

 

[SkipVought]

I give up!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[scooterny]

Skip,

Please don't give up, I could really use the assistance. I would like the non-blank entries in col A to get pulled into Col C, in a row, as in below.

Scott

Col A Col C
------ ------
Foo Foo
giraffe
tire
giraffe pumpkin
rattlesnake
posies
china
tire







pumpkin






rattlesnake





posies





china








baseball




rhino








happyface







gorilla

 

[bluedragon2]

If you do not care what the order is, copy the column then sort it.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[scooterny]

bluedragon2,

I am not sure you are understanding what I am asking, or perhaps I am not making myself clear. I would like to pull this data in automatically, not manually. Right now, the business where I work would use a formula in column C.

So in cell C1 you might have =$A1. In cell C2 you might have =$A2, and so on. In this way, C1 could only contain the value in cell A1, C3 would only have the value in A3, etc. If cell A2 was blank, then C2 would be blank. What I would like to do is have a formula in cell C1 that tests if there is data in cell A1. If there is, pull that data in. If there isn't, go check cell A2. If there is non-blank data in there, pull that in. If there is no data in A2, then check A3 and so on down that column. When the first non-blank cell is found in column A, put that in cell C1. Now I want the search to continue down column A for the next non-blank cell and to put that value in cell C2, etc. I want this to continue until all of column A is checked. Thanks so much.

Scott

 

[SkipVought]

[tt]
C2: if(isblank(a2),c1,a2)
[/tt]
and copy down thru all rows of data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[randy700]

I doubt this can be done without using VBA code. But, there has to be more to this spreadsheet than you're telling us.
It simply doesn't make any sense this way. How about providing some data from ALL COLUMNS of the spreadsheet?


Randy

 

[kwbMitel]

Have you considered just using the formula as is and then applying the Autofilter to remove the blanks?

Why make it more complicated?

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[Kurupt55]

This is a messy solution but it works
insert a column in Column A moving your data to Column B
In cell A1 use this formula
=IF(ISBLANK(B1),"",1)
In B2 Use this formula
=IF(ISBLANK(B2),"",MAX(A$1:A1)+1)
Copy B2 down to the bottom of your data range
In column C Put Numbers 1 to 100 (or as many numbers as you want to ensure you get all the data)
In column D Use this formula
=IF(ISERROR(VLOOKUP(C1,A:B,2,FALSE)),"",VLOOKUP(C1,A:B,2,FALSE))
And copy down to the bottom of your numbers.

This can be tidied up by hiding columns, but this is still not the best way to do this, an autofilter will do the same thing without the mess

Impossible is Nothing

 

[SkipVought]

I would like the non-blank entries in col A to get pulled into Col C, in a row, as in below.

1. Insert a HEADING in row 1!!!!!!!!

2. Select column A

3. Data > Advanced Filter

4. Copy to another location
Copy to: C1
nique records only

VOLA, y'all!




Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[scooterny]

Skip, kwbMitel, Kurupt55, and Randy

Thanks so much for getting back to me. I really appreciate the time and effort you put in to assist me with my issue. Let me start off by saying that most of my problems occur, I think, because of the layout of this spreadsheet. The worksheet I am working with and particularly the column I am trying to work with is of multiple lengths and is a merged column, or at least part of it is. Kind of messy. Perhaps traditional solutions may not work. I think Randy's suggestion that I post the worksheet I am working on is the best way, but I don't know how to attach the spreadsheet in question to this response. I tried most of the things you mentioned, with no success, for varying reasons. Can someone please tell me how to attach a doc to this? Thanks.

Scott

 

[SkipVought]

Select the column containing the data.

UNMERGE.

Proceed as above using AdvancedFilter.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[randy700]

Copy and paste a few line between [ignore]

 and

[/ignore] to get something like this.

Your spreadsheet info here

.

Randy

 

[scooterny]

Randy700,

I took your advice and here is the a part of one of the spreadsheets in question. I was able to successfully copy what I have put between the begining and ending of the code back into a spreadsheet and have it make sense. The only issue with this is that you lose the embeded formulas in the original. So for example, question 9. The second part of question 9 on the spreadsheet is "List of other ways", which comes from column N. So the first entry under "List of other ways" would be =$N6, the second would have =$N7, and so on. The question for me is to get the values of column N in another column without hard-coding which cells in colunn N to look at. Basically, I just want to take the non-blank cells in column N and put them into this Question 9. I am also not sure why I am getting zeros in the blank cells from column N in this question, as the format for the response cells for this question is general. Thanks so much.

Scott

	Program Date	Length of Program	Occupation	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Other?	Race	Income	Age	Sex	Clinician						
1	12/5/2008			5	5	5	5	5	learning the time and energy it takes to do ....

......		
Statistical Questions