# Posts by ajorourke

• ## VLOOKUP partial cell data

Re: VLOOKUP partial cell data

You can use a countif.

e.g

=CountIf(A:A,"MVPN*")

If you want to sum up another column based on the values in Column A you can do a sumif

e.g (Assuming the values you want to sum up are in column B)

=SUMIF(A:A,"MVPN*",B:B)

• ## lookup the minumum value in one column based on value in another.

Re: lookup the minumum value in one column based on value in another.

I tried an Array formula, must have done something wrong with it because this one works fine.

Thanks a bunch!

• ## new record on new row line?

Re: new record on new row line?

I'm assuming you have are entering things into C4, c13, c22, c24, c26 and c27 to create a record and then want the records stored as lines of data?

If this is correct I suggest you have one sheet for data entry and a second sheet for the lines of data.

If you permanently have e4:j4 with the formula you put in the above code and then add the below code it will copy the line of data and create a new record on sheet 2
(where sheet 1 and sheet 2 please change to whatever you want to call the sheets)

• ## lookup the minumum value in one column based on value in another.

I am currently trying to find the first time a record appears in a large set of data.

What I want, is to lookup a reference number that may appear multiple times in a column (A) and return the minimum value in column (B) based on the reference number in Column (C).

So in column D I would have a formula that looks up the value in column C, matches it in column A and returns the minimum value in column B.
E.g
A B C D
X 2 X 2
Y 1 Y 1
X 3 X 2
X 4 X 2
Z 1 X 1

Any ideas?

Thanks in advance!

• ## Macro working on one system but not another - only difference is sp2 vs sp3

Re: Macro working on one system but not another - only difference is sp2 vs sp3

Thanks, I have managed to fix it.

I disabled the error checking by putting On Error Resume Next before the code and found that the macro was doing everything correctly but their system didn't seem to be keeping the copied data in memory. So I added another line of code to copy the data just before pasting it, rather than before opening the file it gets pasted in and it now works a charm!

I still find it strange that it worked for hundreds of other people and just this one person had a problem!

• ## Macro working on one system but not another - only difference is sp2 vs sp3

Re: Macro working on one system but not another - only difference is sp2 vs sp3

It still isn't working, the same line of code just brings up the same error...but only in Italy!

• ## Macro working on one system but not another - only difference is sp2 vs sp3

Re: Macro working on one system but not another - only difference is sp2 vs sp3

Thanks, I tried this and it didn't work. However I noticed while doing this that I was defining the Row before opening the sheet (which worked anyway for everyone else using this file), so I have altered this and sent it to my colleague in Italy to test, I'll likely find out on Monday if it's worked.

• ## Macro working on one system but not another - only difference is sp2 vs sp3

Re: Macro working on one system but not another - only difference is sp2 vs sp3

Also, please note the line of code highlited is actually the paste special - values line.

• ## Macro working on one system but not another - only difference is sp2 vs sp3

Re: Macro working on one system but not another - only difference is sp2 vs sp3

The file opens that the data should be copied into, however they get a run-time error 1004 and the line higlighted in red above is highlighted in the VBA screen.

I work with several countries so I don't think the language settings are the problem. What do you mean by decimal separators? The document has validation on all of the cells that they can edit and it is protected when they run this macro (obviously it gets unprotected within the macro).

• ## Macro working on one system but not another - only difference is sp2 vs sp3

Hello,
Any help with this will be appreciated! I am getting a bit stressed over it.

The below code is working on my system, as well as hundreds of other systems around my business.

However, there is one place it isn't working, (Italy) and the only difference is that their version of excel is Service Pack 3 and mine is Service Pack 2. I have checked Add-Ins etc and they all look the same...

Any ideas?

Thanks!