Copy cells from specific row if the value from another cells match

saloro

New Member
Joined
Jul 31, 2019
Messages
2
Hi guys, I have a problem with my accounting data analysis. I have two workbooks, let's call them "MAIN" and "DATA". Both are exported from another program, that's why they are in separate files, although they can be on separate worhsheets if there is a need. I have to copy cells from "DATA" workbook to "MAIN", based on "Description" column. Copied values have to be from the same row where description was found. Also another difficulty is that description sometimes is not exact, but I need to find all occurrences. I tried to solve this with VLOOKUP and other formulas, the results were poor and when I tried to copy a formula by dragging the fill handle it all messed up.


"MAIN" looks like this:

Date
Account1
Settlement
Dt
Ct
Description
Currency
Transaction type
settlementX
25,00
ABC
settlementX
25,00
DEF
settlementY
39,00
GHI
settlementY
39,00
JKL

<tbody>
</tbody>


"DATA" looks like this.

Account2
Id1
Id2
Document symbolDescription
Dt2
Ct2
Date
Document number
Id3
Id4
Id5
Id6
Id7
111-2-333-33333-5555
00
00000
ABC
25,00
111
2
333
33333
5555
777-2-888-88888-5555
00
00000
JKL

39,00
777
2
888
88888
5555
777-2-333-33333-5555
00
00000
DEF
22,00
777
2
333
33333
5555
777-2-333-33333-5555
01
99999
DEFg

3,00


777
2
333
33333
5555
111-2-333-33333-5555
27
99999
CCC
8,50
111
2
333
33333
5555
111-2-888-88888-5555
00
00000
GHI
39,00

111
2
888
88888
5555
111-2-333-33333-5555
00
00000
DDD
13,49
111
2
333
33333
5555

<tbody>
</tbody>


Result:
Date
Account1
Settlement
Dt
Ct
Description
Currency
Transaction type
Id3
Id4
Id5
Id6
Id7
Id1
Id2
Dt2
Ct2
settlementX
25,00
ABC
111
2
333
33333
5555
00
00000
25,00

settlementX
25,00
DEF
777
2
333
33333
5555
00
00000

22,00
settlementY
39,00
GHI
111
2
888
88888
5555
00
00000
39,00

settlementY
39,00
JKL

777
2
888
88888
5555
00
00000

39,00
settlementX
DEFg
777
2
333
33333
5555
01
99999

3,00

<tbody>
</tbody>


I tried to make it as readable as possible, forgive me if anything is unclear.
I would be very grateful for some help or tips.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,271
Office Version
2013
Platform
Windows
While you can use fuzzy lookup to find similar Description data, since you are dealing with accounting data my advice would be to first edit your Description data so it is standard in both sheets, then do your automation.
 

saloro

New Member
Joined
Jul 31, 2019
Messages
2
While you can use fuzzy lookup to find similar Description data, since you are dealing with accounting data my advice would be to first edit your Description data so it is standard in both sheets, then do your automation.
Thank you for the advice, but I can only use standard Excel version at work, I can't download any add-ins unfortunatelly. I can code my own VBA if this would be the only option, but I don't know VBA very well, that's why I tried different formulas.
 

Forum statistics

Threads
1,081,849
Messages
5,361,681
Members
400,645
Latest member
Zeak

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top