Lukket

index match maybe?

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.

Færdigheder: Excel

Se mere: style manuals help the writer, write a report to help the police in investigation on your encounter with some armed robbers on your way home from a film show, as a secretary i need a book that will help at the office in writing memoradum and other letters, write an android application to help the user find identify and classify colors, i need a designer to help me produce simple prints for clothing, help fix a simple issue with an existing android app, complete the four simple c++, complete the four simple c, simple sort algorithm matlab, index match formula, matlab simple sort algorithm, simple sort script, php simple sort records table, help need quick simple website, excel 2000 simple sort macro

Om arbejdsgiveren:
( 0 bedømmelser ) United Kingdom

Projekt-ID: #15350806

51 freelancers are bidding on average £91 for this job

jaylancer43

Expert Excel Programmer on [url removed, login to view] and have been featured by the freelancer community ([url removed, login to view]) Relevant Skills and Experience I am an E Mere

£150 GBP in 2 dage
(501 bedømmelser)
8.0
£175 GBP på 1 dag
(249 bedømmelser)
7.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 in 3 dage
(162 bedømmelser)
7.2
someka

Hi [url removed, login to view] you could use VLOOKUP but that would need to modify your existing data (every time, sort, move left client ID et.c). Instead, I can set your workbook with necessary formulas. Relevant Skills and Expe Mere

£200 GBP in 4 dage
(134 bedømmelser)
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 £ Mere

£50 GBP in 3 dage
(102 bedømmelser)
6.7
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 Mere

£150 GBP in 3 dage
(203 bedømmelser)
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 Mere

£100 GBP på 1 dag
(170 bedømmelser)
6.6
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 Mere

£25 GBP på 1 dag
(109 bedømmelser)
6.2
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 Mere

£50 GBP på 1 dag
(128 bedømmelser)
6.3
sergpooh

Hello. I understood your task and I can help you. Соответствующие навыки и опыт Excel Предлагаемые промежуточные платежи £50 GBP - Completed task My sample - [url removed, login to view]!354579924015

£50 GBP på 1 dag
(40 bedømmelser)
5.1
ZedExcel

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

£55 GBP på 1 dag
(28 bedømmelser)
5.1
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 Mere

£20 GBP in 0 dage
(43 bedømmelser)
4.5
£100 GBP in 3 dage
(31 bedømmelser)
4.5
SShtv

Your task is clear to me and I am ready to execute the project for the 25GBP. Relevant Skills and Experience I have a successful experience of doing such work. As an example, I bring screenshots of one of the programs Mere

£25 GBP på 1 dag
(21 bedømmelser)
4.5
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 - Mere

£30 GBP på 1 dag
(9 bedømmelser)
3.9
cyash61

Hello there, We are an Analytics Consulting firm headquartered in Mumbai with over 6 years experience in working with excel based data modelling. Website : [url removed, login to view] Please ping for more details.

£180 GBP in 4 dage
(2 bedømmelser)
3.5
£250 GBP in 3 dage
(4 bedømmelser)
3.5
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 Mere

£23 GBP på 1 dag
(12 bedømmelser)
3.0
mpunn2007

A proposal has not yet been provided

£55 GBP på 1 dag
(5 bedømmelser)
2.7
aubrey03

My bid is to provide a solution to your Excel formula problem. I am confident that I can solve it faster than others Relevant Skills and Experience I am an Excel expert specializing in complex formulas and macros. You Mere

£50 GBP på 1 dag
(8 bedømmelser)
2.9