Free Microsoft Excel 2013 Quick Reference

# IF function using Range of data

hey there,

i have just been learning about the IF function and i was just wondering how to use lots of cells such as A1:D10 to check the data and if it matches Plus 1 to a total.

for example

A1 has 200 A2 150 A3 200 in cell D1 it would show 2 (the amount of 200's) that are shown up in the data. (the cell references are just examples as there would be more to be used as i would be doing this for statistics for a bowling team)

hope this is clear and any help would be fantastic :D

THANK YOU

## Related Results

### Using If/Then/Else statements with ranges of data

Hello!
Let me start off by saying I'm an excel newbie (I just started using formulas today).
However, I have a rather complex task I'm trying to accomplish. I have almost no knowledge of excel formulas/coding so I'll try to explain in just words.

I have several ranges of data and I would like to see if any values of this range overlap with another range.

Say column A is the lower limit of one range, B is the upper limit. Column C is the lower limit of a second range, D is the upper limit, etc.

I'm wanting to check if any of these ranges (A to B, or C to D, etc) completely or partially overlap with the range 30<x<=60.

I would like to get a "1" in the cell if there is any overlap and a "0" if there is none.

I'm guessing the code/formula would have to say something like this (please forgive me for not putting this in proper excel language):

If A<=x<=B overlaps with 30<x<=60 then cell = 1
else if C<=x<=D overlaps with 30<x<=60 then cell = 1
else cell = 0

I'm not even sure if this is possible.

Thank you so much!

### Using a Formula to get Range of Data to an Array

Hi,

I have a formula to get a variable range of data from my Task Log worksheet

tempRangeFormula = "='Task Log'!R44C190:R44C" & 190 +

I am trying to use this formula to get an array of values from my Task Log
sheet so
that I can perform some array multiplication using the ScalarMult function
below

Log").Range(tempRangeFormula), Worksheets("Sheet2").Range("E6"))

where the E6 cell contains an integer

I get a Run time error: 1004 application defined or object defined error
appear, it is
something to do with this bit "Range(tempRangeFormula)" I think but I am
stumped. I have tried using the formula directly within the range brackets
and I get a similar erro. I can't work out what is wrong it may just be my
brain is frazzled seeing as its friday and all but if any one can help I
would be grateful.

Chris

### Extracting a range of data from a Pivot Table

I am trying to use the forecast function with data housed in a Pivot Table. I have just begun using the Getpivotdata function and found it to be very helpful. Does anyone know if Getpivotdata can extract a range of data? What I am trying to do write a formula using the Forecast function where the known y's and known x's are a range of data housed in a pivot table rather than having them be absolute cell addresses.

### Using if or sum if to change range of sums

Hi,

I have a spread sheet which compares to ranges of data which changes during
the course of the week.

One range of data compares the current week as it builds against the
previous week and I would like it to compare the same days as the week builds.

Now I can do it manually by expanding the ranges each day, but I am aiming
to automate this so it updates automatically. I believe something can be
done using IF or SUMIF in some way but it is just not working for me at the
moment.

### Copy paste value of the used range of all worksheets

Hi,

I want to write a macro that would copy paste value the used range of all worksheets within the workbook b gor some reasons, I can't get it to work.

I created my own code but it seems like the Range("a1").currentregion does not select all the data in each worksheet. It work for almost all the sheet but some cells still contains formula. Also on some worksheet I get the : "Do you want to replace the current cell" message. How do I override this and make it accept change on each worksheet?

Here is my code:

```
VB:
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim ws As Worksheet

Set ws = ActiveSheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Range("A1").CurrentRegion.Copy
Cells(1, 1).PasteSpecial Paste:=xlPasteValues

Next ws

Application.EnableEvents = True
Application.ScreenUpdating = True
Worksheets("Home").Select
End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```

### Vba code to transpose a dynamic range of data to a new worksheet

Hi, I need help with code for a dynamic range of data. I thought I might be able to figure out something based on help I recently received for a similar issue, but this data set is different I haven't figured out how to solve this one.

I have attached my data sample here. I have a range of values in columns R and S that will change in my future data samples, so the code has to be based on the number of values in this dynamic range. The values in columns R-S are prices, and they are listed in decreasing face value. OR – a second option to base the dynamic range off of are the values in column A; this value is vertically listed repeatedly for as many prices listed in columns R-S. In my current data set attached, there are 7 prices in my dynamic range.

MY DESIRED GOAL: I need the vertical data in columns A-B, R-T to output horizontally onto the “EIS” tab based on the dynamic range. See “EIS” tab in my attachment for an example of what I want the data to look like in the end. My example output uses the first two items from the “Set 1” tab; I used a different color for each item only to better illustrate the sequence. The “discount_key” and “qualifier” need to be listed in columns A-B, respectively. In adjacent cells in column C, the words “applies”, “weekend”, and “weekday” should be hard-coded. In adjacent cells beginning in column D, the price range information should be outputted horizontally. The first of the three rows of data for each “discount_key” should reference column T from the “Set 1” tab (i.e. “Yes” or “No” – which explains whether a discount applies to that price). The second row should output the range of prices for the weekend, coming from “Set 1” tab column S. The third row should output the range of prices for the weekday, coming from “Set 1” tab column R. The code will then go to the next “discount_key” and “qualifier” and repeat the same steps.

This needs to continue until all rows on the “Set 1” tab (i.e. in my sample there are 722 rows of data, but the number of rows will be dynamic in future data) have been outputted into the grid on the “EIS” tab. All data will change in my future samples (except the constant that I know what type of data will be in each column), but the output needs to be based on the dynamic price range (or the number of repeated values listed in column A – whatever is easier to use). If the dynamic range is based on the number of prices listed in columns R/S, it will need to be able to handle up to 24 values maximum.

I don’t have code to work from that applies directly to this issue. All I have at this point are some formulas I have in my attachment on the “Set 1” tab in cells Y2:AA2 to calculate how many values are in the dynamic range. The formula for the # of unique values located in Z2 needs to be dynamic though.

I hope I was able to explain what I’m looking for, please let me know if not. Any help would be GREATLY appreciated!! Thank you!

### Coping ranges of data to a seperate database sheet?

I wonder if anyone out there can help me here?

I am trying without much joy to copy a range of data from one sheet, into another which will act as a simple dbase this will be used as the basis of a pivot table later on.

I'm trying to get the thing to work like the Template wizard with data tracking, as in coping to the database the required items, but instead of linking single cells into the database, i want this to copy a range at a time.................If you get my drift?

I've played around with quite a bit of sample VBA in various books trying to get the damn thing to work, but none seem to work whislt trying to copy a range.

If anyone can help me I'll give them 20% off the price of an Airbus A340-600!

### VBA: Range of data from Excel into Word VBA

I am using Word VBA to import a range of data (may include chart) from Excel. In Word, I used Inlineshape object to act as a Excel object. Once the data is imported, user needs to be able to modify if they wish; however, the file is not linked to original file. I have problem importing the data into excel. The pastespecial method somehow does not work. Here are the code:

Sub Test7()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
On Error GoTo Err_Test
Application.ScreenUpdating = False
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(FileName)
xlBook.Application.Visible = False
xlBook.Application.WindowState = xlMinimized

Dim InShape As InlineShape
Dim objXL As Object
Dim gXL As Excel.Worksheet

:=False, DisplayAsIcon:=False
Set InShape = ActiveDocument.InlineShapes(1)
InShape.Activate
Set objXL = InShape.OLEFormat.Object
Set gXL = objXL.ActiveSheet

' Method 1 succeed only when in debug mode
xlBook.Application.Range("A30:P88").Copy Destination:=gXL.Range("A1")

' Method 2 Fails completely
xlBook.Application.Range("C30:P88").Copy
gXL.PasteSpecial xlPasteValues
xlApp.CutCopyMode = False
SendKeys "{esc}", True

Exit_Test:
xlBook.Close False
xlApp.CutCopyMode = False
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
Set InShape = Nothing
Set xlObj = Nothing
Application.ScreenUpdating = True
Exit Sub
Err_Test:
MsgBox Err.Description
GoTo Exit_Test

End Sub

I have method 1 and method 2, but none of them seems to work.

### How do you move a range of data underneath another? heeelp!!

Hi guys what i need to know is how to move a range of data from one sheet to another but have the 2nd group of data directly under the first group. What i am doing is archiving monthly data and each month is directly under the preceding month (ie, jan, feb, mar etc). So if i had a range of data on a sheet called 'input' (ie range(A2:S85)). How can i code it so that range of data from that sheet is sent to a sheet called 'rkive' and is in the same columns but is inserted one after another (ie sheet position is top to bottom). I was thinking that I will need a loop for the 2nd month as i have used automated macro code for the first month:

Sub mcrRkive()
'
' mcrRkive Macro
' Macro recorded 10/03/2006 by epsheeh
'

'
ActiveWindow.SmallScroll ToRight:=1
Range("E2:F79").Select
Selection.Cut
Sheets("rkive").Select
Range("C2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=40
ActiveWindow.LargeScroll Down:=-1
Sheets("input").Select
ActiveWindow.SmallScroll ToRight:=3
Range("I2:J79").Select
Range("I79").Activate
Selection.Copy
Application.CutCopyMode = False
Selection.Cut
Sheets("rkive").Select
Range("E2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=38
ActiveWindow.LargeScroll Down:=-1
Sheets("input").Select
Range("A2:A79").Select
Selection.Copy
Sheets("rkive").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("input").Select
Range("A83").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("B2:B79").Select
Range("B79").Activate
Selection.Copy
Sheets("rkive").Select
Range("B2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=28
ActiveWindow.LargeScroll Down:=-1
Sheets("input").Select
Range("J18").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Sheets("input").Select
Range("M73:N79").Select
Selection.Cut
Sheets("rkive").Select
ActiveWindow.SmallScroll Down:=3
ActiveWindow.LargeScroll Down:=-1
ActiveWindow.SmallScroll Down:=37
Sheets("rkive").Select
Range("G73").Select
ActiveSheet.Paste
Sheets("input").Select
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.LargeScroll Down:=-4
Range("O2:R79").Select
ActiveWindow.SmallScroll ToRight:=-6
Selection.Cut
Sheets("rkive").Select
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.LargeScroll Down:=-1
Range("I2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=41
ActiveWindow.LargeScroll ToRight:=-1
Sheets("input").Select
ActiveWindow.LargeScroll Down:=-4
ActiveWindow.LargeScroll ToRight:=-2
Range("C2:D79").Select
Selection.Cut
ActiveWindow.LargeScroll Down:=-4
Range("E2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=3
Range("G2:H79").Select
Selection.Cut
ActiveWindow.LargeScroll Down:=-4
Range("I2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
ActiveWindow.LargeScroll Down:=3
ActiveWindow.LargeScroll ToRight:=-2
Sheets("input").Select
Range("S2:S79").Select
Range("S2:S79").Activate
Selection.Cut
Sheets("rkive").Select
Range("M2:M79").Select
ActiveSheet.Paste
End Sub

This code works and inserts the data into the correct columns for the first month. The problem i have is how to insert data for the 2nd month because the automated code references cells which have to be changed obviously to enable the 2nd month to be inserted on the 86th row underneath the first group of data. I was thinking i will need some kind of loop so that after every 85 rows a new month is inserted into the rkive sheet underneath the previous month. I can work it out logically but not sure how to code it:

ie range = range + 85

if range > 0 then
insert range after every 85 rows
end if

Can someone help me out because this is rather urgent!

Thanks !

### Ignoring errors in a range of data and Filtering

Morning All (its morning in Australia),

Im trying to filter a range of data to only show the rows relating to the top 5 values in column W. The formula im using in Excel in conjunction with an advanced filter is as follows:

=IF(OR(W18 = LARGE(\$W\$18:\$W\$62, 1), (W18 = LARGE(\$W\$18:\$W\$62, 2)), (W18 = LARGE(\$W\$18:\$W\$62, 3)), (W18 = LARGE(\$W\$18:\$W\$62, 4)), (W18 = LARGE(\$W\$18:\$W\$62, 5))), TRUE, FALSE)

This works fine though gives an error if any of the values in the range W18:W62 is an error. I am unable to remove all errors in this range (as inserting an ISERROR() in the formula within these cells will make the formulas too long).

Is there a way of having the above formula ignore the cells/rows within W18:W62 that contain errors.

### How to Match and Sort two range of data?

Hi all,

Appreciate if anyone could help out on this issue. I have a range of datas
on sheet 1 and another range of data on sheet 2. Sample as below :

In sheet 1,

Company Name Sales Fig.
D Limited 4000
A Limited 1000
E Limited 5000
B Limited 2000
C Limited 3000

In sheet 2,

Company Name Sales Fig.
E Limited 5000
D Limited 4000
C Limited 3000

I want to match data in sheet 1 to my data in sheet 2 becos my priorty is
the data in my sheet 2.
As such, "A Limited" and "B Limited" must be flag as unmatch entry in sheet
1 and be deleted immediately. On top of these, the data in my sheet 1 must be
sorted the same sequence as sheet 2. That is, in sheet 1 shown as

Company Name Sales Fig.
E Limited 5000
D Limited 4000
C Limited 3000

That is same data as my sheet 2 at last. I cant use custom list in excel
becos my range of data will be as many as few hundred rows

Thanks

### Vlookup to Return a Range of Data

Previously you responded to a question concerning sorting data by project
number. Can this be done for a title, ie XXX-contract1, XXX-contract2,
YYY-Contract1 or some variation of this

Subject: Re: Vlookup to Return a Range of Data 5/29/2006 5:57 PM PST

By: Biff In: microsoft.public.excel.misc

Ok......

Here's how to do this using formulas. You can play around with it and see if

We'll use just the small sample of data you posted.

> A B C D
> 1 Store # Account # Date Amount
> 2 2885 120000 05/17/06 100.00
> 3 2950 130405 06/17/06 50.00
> 4 2885 130402 04/20/04 200.00
> 5 2950 126210 08/17/05 50.00

Assume that data is in a sheet named Dump in the range A1:B5, A1:D1 being

Create a sheet and name it 2885 for store # 2885.

In A1 enter this formula:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

This will return the sheet name 2885. This will also come into play later on
when creating sheets for the other stores.

In B1 enter this formula:

=COUNTIF(Dump!A:A,A1)

This will return the number of rows of data that there are in sheet Dump for
store # 2885.

Enter these column headers in A3:C3: Account #, Date, Amount

Enter this formula in A4. This is an array formula and MUST be entered using
the key combination of CTRL,SHIFT,ENTER:

=IF(ROWS(\$1:1)<=\$B\$1,INDEX(Dump!B\$2:B\$5,SMALL(IF(Dump!\$A\$2:\$A\$5=\$A\$1+0,ROW(Dump!B\$2:B\$5)-ROW(Dump!B\$2)+1),ROWS(\$1:1))),"")

Copy across to C4 then down. You'll need to copy the formula to enough rows
that all the data for store # 2885 is returned. In your reply you say this
typically about 30 to 40 rows, so, copy down maybe 50 rows.

That's it for store # 2885.

Now, let's create sheets for the other stores. This is pretty cool.........

Select sheet 2885.

Right click on the sheet tab.

Select Move or Copy....

Select Create a copy

OK

Select the copied sheet that has the name 2885 (2)

Right click the sheet tab and select Rename.

Rename the sheet 2950 (based on the store numbers of your posted sample
data)

That's it for store # 2950

Now, just repeat this process for the other store #'s.

Biff

"Rob" <Rob@discussions.microsoft.com> wrote in message
news:A1C84469-972F-4CE9-8C14-22D2C7DD6C6B@microsoft.com...
> Hi Biff,
>
>
> *Typically, there will probably be about 30-40 lines of data for a
> specific
> store.
>
> *Over the course of a year, there will be 200 different stores that need
> to
> be analyzed.
>
> *The "Dump" of data can be sorted anyway in which it needs to be (Store #)
>
> *The "Dump" of data will be updated every month.
>
> -Basically, as we open new stores we need to analyze all data for that new
> store. We update this particular file every month - and add new stores
> (tabs)
> as required.
>
> Hope this helps.
> Thanks!
> Rob
>
>
> "Biff" wrote:
>
>> Ok, one more question, well, maybe two or three.....
>>
>> Out of those 4000 rows approximately how many will typically be
>> associated
>> with store # 2885?
>>
>> How many different store #'s are there? I assume you want to extract the
>> data for each different store to its own sheet?
>>
>> Can you sort the "dump" sheet by store # ?
>>
>> Is the "dump" static or does it change on a regular basis (a new "dump"
>> everyday?) Is the size of the "dump" always the same?
>>
>> A lot of questions but this is the type of info that determines your best
>> approach to this. This *could* be done with formulas but it probably
>> isn't
>> the best approach. If you do this regularly I'd turn on the macro
>> recorder
>> then apply a filter and start filtering on the store #'s, copy/paste to
>> the
>> appropriate sheets. Do it once manually and thereafter you have the macro
>> to
>> do it for you automatically.
>>
>> Biff
>>
>> "Rob" <Rob@discussions.microsoft.com> wrote in message
>> news:AB25A1F3-8C42-4CC6-BEE9-D8CDDC1D195F@microsoft.com...
>> > The dump of data starts in A1 and goes to E4000
>> >
>> > "Biff" wrote:
>> >
>> >> How big is "the dump" ?
>> >>
>> >> x columns by x rows
>> >>
>> >> Biff
>> >>
>> >> "Rob" <Rob@discussions.microsoft.com> wrote in message
>> >> news:C3C9CDE2-1A67-4643-8E0D-F881AE0DD95E@microsoft.com...
>> >> > The only issue is this - the "dump" of data is located on a
>> >> > different
>> >> > tab.
>> >> >
>> >> > Example - tab #1 is the dump of data. Tab #2 is a specific store
>> >> > (2885),
>> >> > and
>> >> > tab #3 is another store - and so on.
>> >> >
>> >> > So I basically need to get all the individual store data on a
>> >> > specific
>> >> > tab
>> >> > (based on the criteria).
>> >> >
>> >> > Thanks!
>> >> > Rob
>> >> >
>> >> > "Nick Hodge" wrote:
>> >> >
>> >> >> You should look at setting up an autofilter.
>> >> >>
>> >> >> Data>Filter>Autofilter and filter on the store#
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >> Nick Hodge
>> >> >> Microsoft MVP - Excel
>> >> >> Southampton, England
>> >> >> www.nickhodge.co.uk
>> >> >> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
>> >> >>
>> >> >>
>> >> >> "Rob" <Rob@discussions.microsoft.com> wrote in message
>> >> >> news:DFE1E2CA-57BE-4D44-95A1-4F1E0B0855F5@microsoft.com...
>> >> >> > Hello,
>> >> >> >
>> >> >> > Heres my issue - I have a single piece of criteria (ie - Store
>> >> >> > #),
>> >> >> > and
>> >> >> > given
>> >> >> > this, I need to look at a large dump of data, and return all the
>> >> >> > values
>> >> >> > for
>> >> >> > this particular store number.
>> >> >> >
>> >> >> > Example :
>> >> >> > Here is the data dump :
>> >> >> > A B C D
>> >> >> > Store # Account # Date Amount
>> >> >> > 1 2885 120000 05/17/06 100.00
>> >> >> > 2 2950 130405 06/17/06 50.00
>> >> >> > 3 2885 130402 04/20/04 200.00
>> >> >> > 4 2950 126210 08/17/05 50.00
>> >> >> >
>> >> >> > Now, If I am running a report on Store #2885 - what I need the
>> >> >> > formula
>> >> >> > to
>> >> >> > do
>> >> >> > is give me all pieces of information that relate to that store.
>> >> >> > Here
>> >> >> > is
>> >> >> > what
>> >> >> > the end result of the formula should be :
>> >> >> >
>> >> >> > Store # Account # Date Amount
>> >> >> > 2885 120000 05/17/06 100.00
>> >> >> > 2885 130402 04/20/04 200.00
>> >> >> >
>> >> >> > Can anybody help me ?
>> >> >> >
>> >> >> > Thanks!
>> >> >> > Rob
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

### Applying If in macro to range of data

How would I apply this to an entire range of data? I want all of the columns to stay the same, I just want this to autmatically fill in from row 2 to the end of my range of data.

```    If Range("H2") = ""
Then
Range("J2") = Range("I2")
Else
Range("J2") = Range("H2") & " " & Range("I2")
End If```
Thanks.

### How to Match and Sort two range of data?

Hi all,

Appreciate if anyone could help out on this issue. I have a range of datas
on sheet 1 and another range of data on sheet 2. Sample as below :

In sheet 1,

Company Name Sales Fig.
D Limited 4000
A Limited 1000
E Limited 5000
B Limited 2000
C Limited 3000

In sheet 2,

Company Name Sales Fig.
E Limited 5000
D Limited 4000
C Limited 3000

I want to match data in sheet 1 to my data in sheet 2 becos my priorty is
the data in my sheet 2.
As such, "A Limited" and "B Limited" must be flag as unmatch entry in sheet
1 and be deleted immediately. On top of these, the data in my sheet 1 must be
sorted the same sequence as sheet 2. That is, in sheet 1 shown as

Company Name Sales Fig.
E Limited 5000
D Limited 4000
C Limited 3000

That is same data as my sheet 2 at last. I cant use custom list in excel
becos my range of data will be as many as few hundred rows

Thanks

### Return current date if cells in range have data

Return current date if cells in range have data

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

This is a follow up to see if anyone can help please, the responses from Nika were good but failed to work across a range of columns.

I am wanting to add a function (I think an IF statement) that searches the previous cells and returns the current date if any of the cells contain text or numbers (anything). There are 12 to 14 column cells that may have info in them.

If this is easy it would also be nice if I could have another cell after the Date cell that shows if the previous cells have been edited or changed since the current date (a Date Changed function).

Does anyone know how this can be done please?

I am attaching an example of the cells I am working with, with the date at the end column. Hope this makes it a bit clearer.

Many thanks

Brian

### Help requested with excel worksheet functions accross a range of data values

Brain teaser of note! well for me at any rate, pity the poor sap with an arts background trying to put this together. My grasp of excel formula is remarkably poor to say the least.
I am trying to do the following: I need to check a cell [say A2] for the existence of a range of data and then depending on the range that exists, populate another cell [say B2] with another value.

ie:
if A2 has a value ending in the range 00-09 then B2=9
if A2 has a value ending in the range 10-19 then B2=8
if A2 has a value ending in the range 20-29 then B2=7
if A2 has a value ending in the range 30-39 then B2=6

And so on.

Any help with this on will be hugely appreciated!

### Using VBA to determine different ranges of data set

How to distinguish different data sets with unknown ranges?

For example: Row 1 to Row 56 belongs to Company A. And Row 57 to Row XX belongs to Company B and so on until 100 companies. For each company it has different range of data and we could not determine the number of the companies and the name as well. How could we use VBA to read and distinguish the name of the company in order to perform calculation for each company?

### Looking for Excel function that displays first occurrence of data in a range of data.

First off,

This is my first post at this forum. Herro everyone!

I'm generally able to Google my Excel questions and figure out how to do it, but I'm not sure what to even search for to figure this one out.

I am looking for an excel function that [in a range] will display the first occurance of data in a range of data.

I.e:

Apple
Apple
Apple
Apple
Orange
Orange
Orange
Apple
Tomato
Plum
Plum
Plum
Tomato
Orange

I would like to see:

Apple
Orange
Tomato
Plum

Is this an index function?

### Compare and align a range of data with other and adding blanks either ways

Hi,

Can somebody give me any pointers where I can compare a range of data with another range of data and align accordingly. The data can be in other sheets too. Attached is the excel sheet for the requirement. In sheet 1 there is a range of data DATA1 and DATA 2. If columns A,B,C equals to Columns D,E,F or viceversa. Then data must align with each other. Otherwise, blanks have to be left. The desired result is as shown in "result" sheet of the attachment.

### Automatically sort a range of data

I have a worksheet that has data in it in columns a:i. Rows 1-6 is the title of the worksheet. Row 7 is the header for columns a:i. I would like to have the range of data sorted by column G. This column is the due date for a job. Is there any way that I could have the range automatically sorted by column G when a new row is added with a new due date? I would like the dates to be in order of the soonest due. I.E. if todays date is 4/1/2011. The first would be due 4/3/2011, then 4/8/2011, then 4/11/2011 and so on.

### Printing of non continuous ranges of data

Hi,

I am trying to print non continuous ranges of data in the same worksheet using the print area - by keeping the ctrl button pressed etc... it works fine but the outcome is that the text in the pages becomes very smaller when compared to when you print page by page and fit every page to one.

Can anyone help me since it saves time to print as one whole block but then the presentation is not so good.

Thanks

### Graph a range of data

First time on this site and it is great, here is my question: I am trying to graph a range of data with increments from .05 through 1.55. I can get the graph to include all the data but I want a range on the "X" axis rather then all 90 points. If this makes any sense, how do you change the axis from each point and rather to a range.

Ex: right now it has bars at .05, .06, .07, .08, etc

and I want it to have just 0 .10 .20 .30 .40 and include the data.

### Copy a range of data based on row count

Is it possible to define a macro that will copy a defined range of data, i.e., B1:H10 down a number of times where the number is based on the row count in Col.A, i.e,
Code:
```Range("A65000").End(xlUp).Select
RowNum = ActiveCell.Row```
So that if the last cell count in Col.A is 20 the array is copied 20 times.

I hope I am clear.

Regards

a

### Charting a wide range of data

Is there a way in Excel (I'm using 2000) to make a broken bar in a bar chart. We're charting the comparison of the US economy vs various other nations and have just what you'd expect, a very wide range of data. To show the American economy we have to set the range so high that many smaller nations' figures barely appear. Is there a way to create a break in the US bar, implying that data has been left out, so that the other nations can be displayed.

Thanks.