index match maybe?

已关闭 已发布的 6 年前 货到付款
已关闭 货到付款

I work in a bank looking for an excel solution to a problem.

On day 1 I have two identical tables filed with identical data - so A2 (and B2 and C2 etc) in table 1 = A2 (and B2 and C2 etc) in table 2. Each table has 60 columns and ~100 rows.

Each of the rows represents a different company, best identified by a Client ID (a unique number that never changes) that appears in one of the middle columns. All other company information within a row can change however.

Table 1 will be the most recent data (refreshed every day) and table 2 will be the hard coat version, copy and pasted from Table 1 using the previous day's data.

I have a third table that compares the specific cells within each of the rows within each of the tables and uses an IF function along the lines of:

If A2 in table 1 = A2 in table 2, then "OK" otherwise "Not Okay"; if A3 in table 1 = A3 in table 2, then "OK" otherwise "Not Okay" etc.

On day 1, when the tables and data are identical, the same rows and cells of the same company are being compared.

The problem is the live data in table 1 will include new rows of new companies that can insert themselves anywhere, which means that the company found in row A in table 1 may no longer be the same company found in row A in table 2.

And I can’t just sort both tables’ data by Client ID as a simple solution as table 2 will have rows / companies that no longer appear in the live data in table 1.

I've tried to work out which function I should use to match the rows of the two tables using the Client ID - maybe Index Match - but can't get it to work.

In basic English the solution should be:

In table 1, use the client ID in row A (or B or C or D etc) and return the row in which the same client ID appears in table 2.

Once the correct rows have been matched, then compare A2 to A2, A3 to A3 etc using the IF function mentioned above.

I could potentially move the Client ID all the way to the left in order to use a VLOOKUP but not sure this function will work.

Please help.

Excel

项目ID: #15350806

关于项目

50个方案 远程项目 活跃的6 年前

有50名威客正在参与此工作的竞标,均价£93/小时

jaylancer43

Expert Excel Programmer on freelancer.com and have been featured by the freelancer community (https://www.freelancer.com/community/articles/jay-patkar-freelancer-success-story) Relevant Skills and Experience I am an E 更多

£150 GBP 在2天内
(765条评论)
8.6
deepbluetech

Excel macro based solution will meet your requirement. Relevant Skills and Experience I have expertise in Excel formulas and macros. Proposed Milestones £100 GBP - Delivery and acceptance

£100 GBP 在3天内
(173条评论)
7.4
P0L

Hello, I’m an Excel expert and I would like to help you with your project. I'm fully available and I can start right away. Please check my profile and contact me to discuss. Regards. Stay tuned, I'm still working on 更多

£150 GBP 在3天内
(391条评论)
7.6
ZedExcel

Hi. Please send a sample file that I can work with. I will provide you a solution within a day.

£55GBP 在1天里
(35条评论)
7.1
ksriniravi

I can get the INDEX MATCH formula to work on your workbook - it is the right choice. No need to move Client ID column to the left just for the reason to support VLOOKUP formula. Relevant Skills and Experience 18 years 更多

£25GBP 在1天里
(202条评论)
6.8
elqueabandona

Hello, my name is Cristian, I have a degree in Business and work with excel every day. I have much experience with spreadsheets, formulas, models and macros. Check my reviews. Best regards Habilidades y experiencia 更多

£100GBP 在1天里
(261条评论)
7.0
hdlong66

Hello, We can use Index/Match function for this task. Please share you Excel file so I could return a work for your review. Thank you and Best regards, Hoang Relevant Skills and Experience Excel Proposed Milestones £ 更多

£50 GBP 在3天内
(139条评论)
6.8
avinav606lx

Hey There, Thanks for posting the project. Its looks feasible and am Interested to do it. I am an excel/access VBA automation professional having 7+ years of experience and can do it with the required quality. Nex 更多

£50GBP 在1天里
(160条评论)
6.4
AkilaArun

Hi, I read through the entire requirement. I got an idea of what you are looking for. I would like to see the excel file if possible. That would greatly help. I have two solutions to this. Relevant Skills and Experien 更多

£23GBP 在1天里
(46条评论)
5.6
afenache

Hi, I have over five years experience in using Excel at an advanced level (formulas, macros, automatic calculation sheets) as an Industrial Engineer at Michelin. I am always keen to improve my work quality and produc 更多

£28 GBP 在2天内
(22条评论)
5.3
Taisho

Hello, You are correct that Index + match based on Client ID is a good method to solve this. I can write such function ASAP, provided sample data and use it it this file. Regards Relevant Skills and Experience I'm a 更多

£20 GBP 在0天内
(60条评论)
5.3
alexanderrubtsov

Excel has functions for searching columns/rows, returning index position. This may be used for solving your task. Relevant Skills and Experience Everyday use of Excel for about 15 years. Proposed Milestones £30 GBP - 更多

£30GBP 在1天里
(12条评论)
4.2
samerbadee

Hello.. it seems to me not a big deal. I may need to see the file and discuss other constraints

£60 GBP 在2天内
(4条评论)
3.3
parth1330

I am a professional typist. I am having a typing speed of 45-50 words per minute with perfect spelling. I am having experience of data entry of 3 years. I can handle this project with much ease Relevant Skills and Exp 更多

£23 GBP 在3天内
(8条评论)
3.0
mpunn2007

A proposal has not yet been provided

£55GBP 在1天里
(8条评论)
3.1
bondilimahesh

I have extensive experience working on projects that require excel VBA skills. I'm keen on quality and delivery of project well advance in time. Relevant Skills and Experience Excel, VBA Proposed Milestones £55 GBP - 更多

£55 GBP 在3天内
(2条评论)
2.4
nickret16

Hi there, you will need my expertise on this excel template. I can deliver the job fast and working. Thank you Relevant Skills and Experience Excel

£80 GBP 在3天内
(4条评论)
2.0
pencitku

First I will concatenate the field, then vlookup the value. I can do this task right now for about an hour. feel free to contact me first. Thank you Relevant Skills and Experience excel function, vlookup, hlookup, cou 更多

£20 GBP 在0天内
(4条评论)
2.1
Abubakar121121

Hi, Sir I am interested in your project If you want to hire me please reply me. Stay tuned, I'm still working on this proposal.

£23GBP 在1天里
(5条评论)
1.8
latchu234

i propose to complete the work in a day for 20 GBP. I ve completed my PhD with much of my research work done with the help of Office automation toolsl like excel, vba & macros. Looking forward to hear from you. !!!

£23GBP 在1天里
(3条评论)
1.4