-2

I am trying to create code that will help me achieve the correct format. All I want is to pull employee (EE) entire row and paste into a new sheet, and then second part (ER) value to match the same employee.

In other words, if employee pay pension contributions for 100 (Pen EE (Tal)) and employer pay for 200 (Pen ERS (Tal)) on behalf of the same employee. Then, the worksheet should look like Column A= Pers No, Column B=Employee Name, Column C= ID Number, Column D=PenPay, Column E=Pens EE(Tal) and Column F=Pens ER(Tal) and G= Total (Pen EE +Pen ER values), as exactly in example tab.

I hope this makes sense. I have tried several methods but no luck so far. Thank you all for your help.

Community
  • 1
  • 1
Mouctar
  • 1
  • 3
  • Did you have a look at the SQL support for Excel in VBA via ADODB? That would probably be the cleanest solution. – M.Doerner Dec 29 '16 at 00:23
  • Hi, no i have not. could you please post the link so that i can take a look at it. I am fairly new on VBA coding processing. – Mouctar Dec 29 '16 at 10:26

1 Answers1

1

From your problem description, I think I can decipher that you are using Excel.

I see three basic approaches to solving your problem:

The simplest approach is not to use VBA at all and instead use the workbook functions INDEX and MATCH. The latter returns the row or column where an exact match is found in the search range, which has to be one dimensional. (The exact match actually requires the search option 0.) The function INDEX returns the value at the given row and column in a range. Combining both, you get a more flexible verision of VLOOKUP and HLOOKUP, which you can use to find the values in the second table for a given employee. (MATCH returns an N/A error if no match is found.)

The second approach is to use the Excel object model in VBA. You can search a range for a value using the Find method of the range. This returns the cell where the match is found and Nothing in case there is no match. If the search result is not Nothing, you can use the EntireRow property to get the entire row and the Cells property to select cells in the row.

Finally, you can use the support of SQL for Excel via ADODB. For a description how to do this, you can look at this Microsoft page and this old question on SO. Provided you know how to write SQL queries, this should enable you to solve your problem via SQL.

Community
  • 1
  • 1
M.Doerner
  • 712
  • 3
  • 7
  • @Mouctar I agree. Using INDEX and MATCH can be slow given enough data. In this case you might want to have a look at the third approach. The SQL support uses more efficient join algorithms to match your data. – M.Doerner Dec 31 '16 at 11:54
  • Hello, thank you for your advise. However, i am currently using Index and Match to get the correct format and it takes me for ever to do them, as i have over 70 pension schemes and approx 7000 employees, so this option is good for small reports but not big reports. The second suggestion might be better option but i am struggling to even think where to start from, as i am fairly new on VBA coding. Final suggestion be might good too but it is first time i hear about it, so that would take me forever to understand, never mind writing code that would do the job. – Mouctar Dec 31 '16 at 11:56
  • Any idea where i can find learning material about SQL, i am just want to make my life better at work, as it can be stressful as the deadline is short turn around. Please any help will be much appreciated – Mouctar Dec 31 '16 at 12:05
  • Hi, Please see below layout. A B C D E F Pers.No Name ID Number Pension Type Pensionable Pay Amount Net Amount 008835 Jack Wilson NE54315 Pens EE(Tal) 827.57 45.52 008835 Jack Wilson NE54315 Pens ER(Tal) 827.57 162.20 I need to a code that can do below example lay out. I am fairly new on VBA coding A B C D E F G Pers.No Name ID Number Pensionable Pay Amount Pens EE(Tal) Pens ER(Tal) Total 008835 Jack Wilson NE54315 827.57 45.52 162.20 207.72 (45.52+162.20) Thank you again for you help – Mouctar Dec 31 '16 at 12:50
  • @Moucart For some reason our estate on SQL you can look [here] (https://www.quora.com/How-do-I-learn-SQL). – M.Doerner Dec 31 '16 at 12:52