MS Excel / VBA help thread

Basic MS excel here I'm sure, but how do you take a column of figures with some duplicate figures in the column to combine the duplicates and make the the duplicates one count overall in the column?

Cheers

Sort them then add and of formula? =if(b1,a1,1,0) then paste values and sort by that column anything with a 0 won't be duplicated.

Is that what you mean?

Data - Data tools - remove duplicates

Thanks both, I will play. If column has 10 rows and 4 rows are duplicates of one number then I want the final row count to be 7 not 10. I have to apply this to 57000 rows with numerous duplicates. Does this make sense?

remove duplicates is what you want

I have 2 tables and (for the sake of example) both have a customerID and PurchaseDescription column.

I want to add an additional 'does it match' type column in the 2nd table to see if the PurchaseDescriptions match in both tables, for each customerID.

The customerIDs arent in any order and there may be CustomerIDs that arent in both tables.

How can I do this? vlookups? help would be greatly appreciated...

If statement. Or Vlook up.

If statement. Or Vlook up.

A little help with the vlookup please? i'm rusty...

you need your data in columns, with the 'index' column on the extreme left. The other fields in the function tell it which column you want it to return data from (counting from the left) and whether an exact match is required. And of course which variable you want to trawl for in the index.

=vlookup(lookup value,table array, col index number,exact match required)

Hopefully this will help out:

a b c
1 cat 4
2 dog 45
3 ham 24
4 egg 67

Let's say I want to look up how many pieces of Ham I've sold.

The formula below is in column B1 with the Value to look up in A1

=Vlookup(value to look up in this case A1, where to look up A4:C4,how many rows across is the data to return? 2 in this case, False (generally you want to return an exact amount)

a,b, c... ...it's easy as ham, dog, egg.

That was the original before those Jacksons ruined it!!

Awesome, thanks spenceey and Olly398!

Vertical dot plots. anyone?

1 data field. Let's call it DOB.
33 Centres get an overall % score for completing this field.

I should be able to have percentage on the Y, DOB on the X. And then plot each of the scores from each of the 33 centres on a single line. Giving me the distribution of scores from each centre.
That makes sense?
Why can't I do this simply on Excel?
I mean, I can draw the graph physically with pen and paper.
What is it in excel?

^Is that how you want your graph to look? I'm not sure that's particularly helpful/representative (i.e. what happens if 3 or 4 dot's have the same score? They get lost...)

It's been a while since I've done any charting but I imagine you can do it as a stacked bar chart - where the x axis value for each point is actually the sum of all the points underneath.

Then give the bars no fill, and adjust the bar outline to also have no fill - there's an option somewhere to put a dot on the top of the bar portions. I'll have a go and see if I can make it work...

Choose scatter diagram, but put a column of constants next to your data field?

I'd do a Stock chart, maybe the Open-High-Low-Close?

I have a quick & dirty way to do this

Can't upload the file or image, but:

Scatter plot
Series data:
Series name=Sheet1!A2
Series X values=Sheet1!B1:K1
Series Y values=Sheet1!B2:K2

Where the values in
A2 = "Antibacterial Soap"
B1:K1 = all have "1"
B2:K2 = your observation data

Other series:
Series name=Sheet1!A5 = "Normal Soap"
Series X values=Sheet1!B4:K4 = "2" for all cells
Series Y values=Sheet1!B4:K4 = observation data

^Is that how you want your graph to look? I'm not sure that's particularly helpful/representative (i.e. what happens if 3 or 4 dot's have the same score? They get lost...)

http://www.cdc.gov/osels/scientific_edu/ss1978/lesson4/Section4.html

Cheers TW2.

It's been a while since I've done any charting but I imagine you can do it as a stacked bar chart - where the x axis value for each point is actually the sum of all the points underneath.

Then give the bars no fill, and adjust the bar outline to also have no fill - there's an option somewhere to put a dot on the top of the bar portions. I'll have a go and see if I can make it work...

I've not tried TW2;s method, it's probably easier, if not:

Your data:
antibacterial soap |plain soap |control
0.5 |1 |2
1 |1.5 |3
1.2 |1.7 |3.5
1.5 |3 |4

Make a new set of data as each point in yours minus the previous one:
antibacterial soap |plain soap |control
0.5 |1 |2
0.5 |0.5 |1
0.2 |0.2 |0.5
0.3 |1.3 |0.5

Select the second table
Insert Column chart
Select all chart types
Select 'stacked line with markers'

right click on graph, 'Select Data', 'Switch / Row/Column'
Then delete all the lines, so you're just left with the points on the chart

TW2: that's working really well.

following on from this.
Is this the quickest, simplest, way to do horizontal dot plots?
http://exceluser.com/excel_dashboards/dotplot.htm

I need to do multiple selection same cell drop down lists.
Easily done by following this tutorial.

However, it only works in column C. I see in the VB code it refers to column 3 alone, so what do i need to edit in the VB code to make it apply to columns C,D,E,F,G...?