How Do I Condense A List To Have No Blanks

Dec 27, 2006

I think this must be fairly easy to do. I've found various posts here and tips elsewhere that indicate ways to do this, but I've not found one I could get to work.

I have a column with numbers in it. These numbers are formatted as TEXT (because they are 001, 002, etc). This list is generated by pulling the unique values from another column where these numbers are repeated.

In the following example, B has the original data. H has the same data with just the unique values. That is how far I have got. What I now want is what you see in Column I (the unique values with NO BLANKS in it).


B | H | I |
-------------------------------------------
001 001 001
001 002
001 003
002 002
002
002
003 003
003
003

I am aware that I can use an Advanced Filter (not that I manage to get that to work without it generate an erro). But this is not what I want, as I'd like the data in I to be dynamically generated such that if more unique values are added to B then H will reflect this (it does that at present) and then I will also reflect this.

View 9 Replies


ADVERTISEMENT

Condense Column Q To Column S No Blanks No Zeros?

Dec 12, 2013

Would like to Condense Column Q to Col S no Blanks no Zero's

Tried formulas on net but won't work for me ? ?

View 3 Replies View Related

Exclude Blanks In Validation List From Horizontal List

Apr 7, 2013

I would like to exclude blanks from the source of a validation list, I have found a really neat solution for vertical list in this topic, I have modified it, but I have missed something, the formula is not working.

View 2 Replies View Related

Remove Blanks From A List

Jul 10, 2009

Data is in a horizantal list, say row 2:

A2: SAM
B2:
C2: MARY
D2: JOE
E2:
F2: DAViD

i.e. like this:

SAM-blank-MARY-JOE-blank-DAVID

I am looking for formulas (not VBA) to compact this list by removing the blanks so the new will be placed in K2, L2, M2 etc. as follows:

SAM-MARY-JOE-DAVID

This is a good article but only works on vertical lists:
[url]

View 12 Replies View Related

Remove Blanks From List

Oct 24, 2006

I need to generate a list of items from a column of data which frequently changes. In the attached example, I need to list all entries other than those that are blank (NB - the data generates the numbers as text and the blanks as "").

View 8 Replies View Related

Consolidated List With No Blanks

Dec 20, 2006

I have a master list from which certain items are chosen and displayed in several other lists based on always changing conditions. The way i have these other lists set up displays the items in the same row as the master list using IF Formula nested with Exact functions to qualify it. If the item isn't qualified to be in a list then I get a blank cell. Is there any way to consolidate these lists so they don't have lots of blank cells?? I would like it to happen automatically as my lists are always changing.

View 3 Replies View Related

Fill Blanks In List

Sep 19, 2007

How to fill or copy contents of

View 4 Replies View Related

Looking To Condense Rows

May 29, 2009

I've got a worksheet with a 1-row gap in between every two rows, and I'm hoping to delete those gaps. In the attached spreadsheet, worksheet 1 (Initial) is what I'm starting with, and worksheet 2 (What's wanted) is what I hope to achieve.

Now obviously, if it were only 10 lines of actual info, I'd just cut/paste. But, I've got ~950 rows of info.

View 2 Replies View Related

Formula To Populate A List Without Blanks?

Aug 1, 2014

I have a list of data with 2 column. One is a column of unique values, the other column has a lot of blanks.

I'd like to auto generate both columns without any of the blank data from column 2. I've attached a mock spreadsheet of what I'm invisioning.

View 6 Replies View Related

Data Validation List :: Blanks?

Mar 6, 2009

I have created data validation list which has 3 flags (Yes, No and Blank). Normally the user will select either Yes or No from column C but i want each time there is no value in either row A or B; i want the code to automatically to select Blank. So basically every time row A or B are blank i want the corresponding row C to be selected "Blank". See attached for more details.

View 12 Replies View Related

Copy List And Skip Blanks

Mar 24, 2014

Is it possible to insert a formula that skips blanks while copying a list? For example: If I have a list and make a column next to the list to put an "x" in if I want to see that info in another list, is there a way to pull the info over with a formula and skip blanks to create a clean list?Color

Include in new List?

Revised List

Red
x
Red

Green

Blue

Blue

[code]....

View 1 Replies View Related

Create Drop Down List With No Blanks

May 19, 2009

I would like to create a list with reference to IF (E1:E150=1) list F1:F150
No Blanks. Cells E1:E150 will be either a 1 or a blank.
The new list will be created in cells Y5:Y whatever. List will then become a drop down list.

I have created the list but it has blanks between names and I just want the names....

View 9 Replies View Related

Fill Blanks In List With Cell Above

Aug 12, 2006

at present i have data in column A That could look like

a1 = fred
a2 to a6 is blank
a7 =bill
a8 is blank
a9 = steve
a10 to a 20 is blank

what the idea is that a1 get copied to a2,a3,a4,a5,a6
a7 get copied to a8
a9 get copied to a10 to a20

these cells could vary on what information is in them and what cells contain information or not

my macro goes to a1 looks for the last cell with data in it then copies itto the cell below then loops back to a1 ans repeats itself

this macro takes about 1 minute to run as the column has about 5000 cells being used. is there a faster way to do this

View 9 Replies View Related

Exclude Blanks In Validation List

Aug 30, 2007

Is there any way to exclude the blank in data validation-under validation criteria-lists. an example is include.

View 3 Replies View Related

Stop Blanks In Drop Down List

Dec 20, 2007

I've got a large selection of lists on drop down menu's the second drop down is based on the selection of the first but when you select the second drop down it starts at the bottom with lots of blanks you have to scroll up to get to the top of the list how do I make this start at the top and ignore the blanks?

I've got the ignore blanks box ticked on the data validation but it doesn't make any difference?

View 9 Replies View Related

Condense SUM Of VLOOKUP Formula

Dec 22, 2009

Is there a way to make this a bit shorter without altering the result.

IF(OR(AI10="",ISERR(VLOOKUP(AI10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))),0,VLOOKUP(AI10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))
+IF(OR(AK10="",ISERR(VLOOKUP(AK10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))),0,VLOOKUP(AK10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))
+IF(OR(AM10="",ISERR(VLOOKUP(AM10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))),0,VLOOKUP(AM10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))

I initially had a SUMPRODUCT formula that looked like this:
=SUMPRODUCT(--(ISNUMBER(MATCH('Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79,F1:F5,0))),'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79)

However, I turned the page into a dynamic page where the month can be selected from a drop down menu and changes the column index number in Cell F8. And because VLOOKUP can give me an error result if there is a mismatch, I used that combination of Blank cells or ISERR. In the range AI10:AM10 there should be three lookup values (sometimes only one or two), and empty columns between the three values columns. I also will use this formula like a 1,000 times so, arrays is not advisable.

View 3 Replies View Related

Condense 11 Rows Of Data Into 4

Oct 20, 2006

I have attached a workbook where the data in columns D:N is collected from another source using Vlookup. In Row 5 There are three numbers 60, 74 & 93. I need a way of condensing those 11 rows down and getting those numbers to appear in a summary for the week (the yellow box) on the right.

View 6 Replies View Related

Populating A List Without Blanks Based On If Statement

Apr 17, 2009

I have created a checklist using "Marlett" checkboxes. I have the names of choices in column B the Checkboxes in column C. In another sheet I want to populate only the names of the choices chosen without any of the spaces that a traditional if statement would populate if it was copied down a column.

View 3 Replies View Related

Validation List Ignore Blanks (formula)

Jul 16, 2009

I want to create a validation list with value comming from formula and I got blanks line. I have to create a dynamic list, I can have 15 possibility and it came from a lookup formula. When I choose the 15 cell with the lookup formula it show the empty cell. I did a exemple of my problem. You can check the attachement to understand my problem in the cell C1.

View 2 Replies View Related

Sequentially Number List Ignoring Blanks?

Feb 7, 2014

I have a column of week numbers but some gaps in the list (e.g. the job is complete and so I do not want to reference it) I am trying to create a list of jobs by week number. I need to sequentially number jobs to then use Vlookup e.g. job1-week1 job2-week1 etc to display be week.

I can work out to number the list ignoring the blanks but then resetting to 1 with each new week?

Workbook1.xlsx

View 2 Replies View Related

List First Instance Only Ignoring Blanks In ROWS

Dec 11, 2008

I have a set up of investments. Say A1 through to G1. Then on the next row the investments range from A1 to G1 again. This continues down many rows. There are spaces such as D1 and D2 through E2.

A
B
C
D
E
F
G
1
ausbil........................

Starting in column H, I would like to list the investments but ignore investments that have already appeared on the row (ie list only the first occurence) and ignore the blanks as well. For example, D1, E1, F1 are to be ignored because of the blank, and the fact that ausbil and amp already appear.
In row 2, the blanks in D2 and E2 are ignored as well as the repeated 'amp' in F2. So the table should look like below from column H:

H
I
J
K
1
ausbil
amp
vanguard
bt
2
amp
becton
fidelity
bt

Is there a simple formula for this? It has to be in rows as I have at least 60 columns of data before these columns begin. There are at least 300 rows.

View 9 Replies View Related

Creating Single List With No Blanks From Multiple Columns?

May 2, 2014

I typically use the following formula to create a list with no blanks, when I have a single column of data. Is there a way to do the same thing when you have multiple columns of data (side by side to make it simple) and you want to create a master list that gets rid of the blanks, and keeps them in the order they appear (by column)?

[Code] .....

For example, if I had data (with some blank cells randomly placed) in columns AM and AN, and I wanted a singe master list in one column of all non-blank cells in column AL, followed by column AM, etc.? Is there a formula to do this, or must I resort to a macro?

View 2 Replies View Related

Excel 2003 :: Ignore Blanks When Sorting A List?

Aug 24, 2012

I am using Excel 2003.

I have a pivot table in sheet1 and references in sheet2 like

Code:
='Sheet1'!A1
and so on to copy the whole thing to make it the source data for a bubble chart.

Now, I want to convert the table in sheet2 into a list via Ctrl+L to be able to sort by names with a dropdown menu. Unfortunately, I have to copy all rows from 1 to 1000 to account for possible increases in the pivot table size. This results in blanks in the list and when I want to sort it, I have 990 blanks before the first data rows show up. Not very neat

View 1 Replies View Related

Drop-Down List With Variable Range And Hiding Blanks

May 4, 2007

I currently have a list on a worksheet named "Options". It is a named range entitled "Type" with rows A2:A500. Another worksheet uses "Type" as a drop-down list. Currently there are only five entries in the list - cells A2:A6. Other items will periodically be added to the list. That is the reason for the long range - up to A500.

In the drop-down list, all of the unused cells in the named range show up as blanks. Is there any way to not have the blanks show up in the drop-down list?

Or...

Is there a way to define the named range as a variable range to enable users to add items without redefining the range and have the added items automatically show up in the drop-down list?

View 9 Replies View Related

Macro Or Formula To Condense Or Consolidate Ranges

Apr 9, 2007

I have a spreadsheet with over 1000 rows of information.

There are two columns FROM_ACCT_NO and THRU_ACCT_NO.

I need to create a macro that will give me a condensed account ranges.

Example the first range would be 2000000 to 2000003.

The next range would be 2000005 to 2000298.

And so on, and so on etc....

View 9 Replies View Related

Excel 2010 :: Not Show Blanks In Dropdown List With AutoFilter?

Jul 21, 2014

How can I get the dropdown list to not include blank cells in the list reguardless if the sort filter is used or not.

Sheet 1 (STATS) is the dropdownlist Cell B12
Sheet 2 (Orders) is the data, I cant convert to tables because the cells are active and storted Column B2: is the data named.

so I have to use a formula to do this.

Using Excel 2010

See attached sample
Sample.xlsm

View 7 Replies View Related

Remove Blanks In Data Validation Dropdown List With A Condition

May 13, 2012

I would like to be able to make the same thing and select items for my drop down list, that are not blak and follow a specific condition. But I don't know how to include the condition in the array formula.

The condition I need is follow:

I want to have a dropdown list with Items that are not blank, and First N > Last N.

[URL]

It would be preferred to have formulas.

View 5 Replies View Related

Condense Code To Make More Efficient And Run Until Blank Cell

Apr 10, 2014

Any way of shortening the attached code. The first part 'Sub Loop1()' works great but I can't figure out how to shorten the second part. As you can see the distance between columns is always consistent i.e. add 5 columns to find the next outcome to work out the next result. Ideally I wanted to somehow loop through the next 5 columns again and again until the cell is blank.

[Code] ......

View 2 Replies View Related

Create List Of Uppercase Data From A Range Excluding Blanks And Errors

May 8, 2014

I have a list generator that creates a set of data in a multi-column & row dataset. I would like a formula to create a list of the alpha data points only which excludes blanks and any errors.

Example:
AADD
#N/A
BB23EEFF
#N/A
#N/A12GG
CC

Results:
AA
BB
CC
DD
EE
FF
GG

View 7 Replies View Related

Create Column List Out Of Table (rows And Columns) While Removing Blanks

May 26, 2014

I am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple.

Formula:

[Code] .....

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved