Home

Multiple csv files - combine select data into one sheet

Darin A
New Contributor

Content:  3 different csv files

File1.csv contains desired data (1-1-1-1234567) in column C

File2.csv contains desired data (1234567-GE-1) in column C

File3.csv contains desired data (1-1-1-1234567-GE-1-1) in column I (eye, not L)

 

Each file is copied into Excel workbook as it's own Sheet.

The 'key' data is 1234567 and sometimes 123456

 

Attempting to FIND/SEARCH/VLOOKUP/INDEX the 'key' data in each sheet and return it's value along with the associated column data from each sheet.

 

For instance, If 1234567 exists in all 3 sheets - return associated row data from column E, F (Sheet1), column D, E, and F (Sheet2), and column G, E (Sheet3) in/to Sheet4.

 

Desired Results: Sheet4

101101b3718  12(@DHCPatriot) 16(@100m/50m)1776000001788 Reeks 100/50menabled-no-alarms
10110264e1a7898215 12(@DHCPatriot)2(@50m/25m)1776000000163 Shilak 50/25menabled-no-alarms
10110339c2cc7898028 4(@DHCP-CGN) 2(@50m/25m)30000009727 Stalker 50/25menabled-no-alarms
101104640307894823 12(@DHCPatriot) 2(@50m/25m)1776 enabled-no-alarms
1011056a3f87895393 12(@DHCPatriot) 16(@100m/50m)1776000002552 Fetcher 50/25menabled-no-alarms

 

Data:

File1.csv/Sheet1

COE7_20 C.O.1-1-1-101101enabledb3718   1-1-1-103(710GX)-30-7enable30disabled1-1-1-1-1-1710GXCXNKBVL3ANBFAA56410169na2.478-26991975SF10.8.60.710.8.40.10TRUE                 00:06:31:64:f4:5900:06:31:64:f4:5a8.12E+1000non-omci
COE7_20 C.O.1-1-1-101102enabled64e1a7882211  1-1-1-103(710GX)-30-7enable30disabled1-1-1-1-1-1710GXCXNKBVL3AKXFAA56410131na2.478-28991641SF10.8.60.710.8.40.10TRUE                 00:06:31:3d:9c:e400:06:31:3d:9c:e58.11E+1000non-omci
COE7_20 C.O.1-1-1-101103enabled39c2cc7882212  1-1-1-138(711GE)-30-7enable30disabled1-1-1-1-1-1711GECXNKBVM9900ARD56410495-22.6162.478-27992144S810.8.60.7 TRUE                 ec:4f:82:e3:13:f8ec:4f:82:e3:13:f92.12E+1100non-omci
COE7_20 C.O.1-1-1-101104enabled640307884422  1-1-1-103(710GX)-30-7disable30disabled1-1-1-1-1-1710GXCXNKBVL3AKXFAA56410136na2.478-26991713SF10.8.60.710.8.40.10TRUE                 00:06:31:3b:af:1a00:06:31:3b:af:1b8.11E+1000non-omci
COE7_20 C.O.1-1-1-101105enabled6a3f87883366  1-1-1-103(710GX)-30-7enable30disabled1-1-1-1-1-1710GXCXNKBVL3AKXFAA56410103na2.478-27992008SF10.8.60.710.8.40.10TRUE                 00:06:31:3e:e7:7800:06:31:3e:e7:798.11E+1000non-omci

 

File2.csv/Sheet2

COE7_20 C.O. 12(@DHCPatriot)16(@100m/50m)1776Not Used 1-1-1-101101-GE-1-1fixednonenonenonenoneenabled
COE7_20 C.O. 12(@DHCPatriot)2(@50m/25m)1776Not Used 1-1-1-101102-GE-1-1derivednonenonenonenoneenabled
COE7_20 C.O. 4(@DHCP-CGN)2(@50m/25m)30Not Used 1-1-1-101103-GE-1-1derivednonenonenonenoneenabled
COE7_20 C.O. 12(@DHCPatriot)2(@50m/25m)1776Not Used 1-1-1-101104-GE-1-1fixednonenonenonenoneenabled
COE7_20 C.O. 12(@DHCPatriot)16(@100m/50m)1776Not Used 1-1-1-101105-GE-1-1derivednonenonenonenone

enabled

 

File3.csv/Sheet3

COE7_20 C.O.101101-GE-1 enabled-no-alarms 000001788 Reeks 100/50m1full1(system-default)NNN nonenoneuniDscpMap:1(access)1-1-101101automedium N
COE7_20 C.O.101102-GE-1 enabled-no-alarms 000000163 Shilak 50/25m1full1(system-default)YNN nonenoneuniDscpMap:1(access)1-1-101102automedium N
COE7_20 C.O.101103-GE-1 enabled-no-alarms 000009727 Stalker 50/25m1full1(system-default)YNN nonenoneuniDscpMap:1(access)1-1-101103automedium N
COE7_20 C.O.101103-GE-2 enabled-no-alarms  1full1(system-default)YNN nonenoneuniDscpMap:1(access)1-1-101103automedium N
COE7_20 C.O.101104-GE-1 enabled-no-alarms 000002552 Fetcher 50/25m1full1(system-default)NNN nonenoneuniDscpMap:1(access)1-1-101104automedium N

 

 

2 Replies

Hi Darin,

 

It looks like most suitable tool here is Power Query if you consider that option.

Power Query sounds...powerful. I will download Power Query and see what it unleashes.