Forum Discussion
Multiple csv files - combine select data into one sheet
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
| 101101 | b3718 | 12(@DHCPatriot) | 16(@100m/50m) | 1776 | 000001788 Reeks 100/50m | enabled-no-alarms | |
| 101102 | 64e1a | 7898215 | 12(@DHCPatriot) | 2(@50m/25m) | 1776 | 000000163 Shilak 50/25m | enabled-no-alarms |
| 101103 | 39c2cc | 7898028 | 4(@DHCP-CGN) | 2(@50m/25m) | 30 | 000009727 Stalker 50/25m | enabled-no-alarms |
| 101104 | 64030 | 7894823 | 12(@DHCPatriot) | 2(@50m/25m) | 1776 | enabled-no-alarms | |
| 101105 | 6a3f8 | 7895393 | 12(@DHCPatriot) | 16(@100m/50m) | 1776 | 000002552 Fetcher 50/25m | enabled-no-alarms |
Data:
File1.csv/Sheet1
| CO | E7_20 C.O. | 1-1-1-101101 | enabled | b3718 | 1-1-1-103(710GX) | -30 | -7 | enable | 30 | disabled | 1-1-1-1-1-1 | 710GX | CXNK | BVL3ANBFAA | 5 | 6410169 | na | 2.478 | -26 | 9 | 9 | 1975 | SF | 10.8.60.7 | 10.8.40.10 | TRUE | 00:06:31:64:f4:59 | 00:06:31:64:f4:5a | 8.12E+10 | 0 | 0 | non-omci | |||||||
| CO | E7_20 C.O. | 1-1-1-101102 | enabled | 64e1a | 7882211 | 1-1-1-103(710GX) | -30 | -7 | enable | 30 | disabled | 1-1-1-1-1-1 | 710GX | CXNK | BVL3AKXFAA | 5 | 6410131 | na | 2.478 | -28 | 9 | 9 | 1641 | SF | 10.8.60.7 | 10.8.40.10 | TRUE | 00:06:31:3d:9c:e4 | 00:06:31:3d:9c:e5 | 8.11E+10 | 0 | 0 | non-omci | ||||||
| CO | E7_20 C.O. | 1-1-1-101103 | enabled | 39c2cc | 7882212 | 1-1-1-138(711GE) | -30 | -7 | enable | 30 | disabled | 1-1-1-1-1-1 | 711GE | CXNK | BVM9900ARD | 5 | 6410495 | -22.616 | 2.478 | -27 | 9 | 9 | 2144 | S8 | 10.8.60.7 | TRUE | ec:4f:82:e3:13:f8 | ec:4f:82:e3:13:f9 | 2.12E+11 | 0 | 0 | non-omci | |||||||
| CO | E7_20 C.O. | 1-1-1-101104 | enabled | 64030 | 7884422 | 1-1-1-103(710GX) | -30 | -7 | disable | 30 | disabled | 1-1-1-1-1-1 | 710GX | CXNK | BVL3AKXFAA | 5 | 6410136 | na | 2.478 | -26 | 9 | 9 | 1713 | SF | 10.8.60.7 | 10.8.40.10 | TRUE | 00:06:31:3b:af:1a | 00:06:31:3b:af:1b | 8.11E+10 | 0 | 0 | non-omci | ||||||
| CO | E7_20 C.O. | 1-1-1-101105 | enabled | 6a3f8 | 7883366 | 1-1-1-103(710GX) | -30 | -7 | enable | 30 | disabled | 1-1-1-1-1-1 | 710GX | CXNK | BVL3AKXFAA | 5 | 6410103 | na | 2.478 | -27 | 9 | 9 | 2008 | SF | 10.8.60.7 | 10.8.40.10 | TRUE | 00:06:31:3e:e7:78 | 00:06:31:3e:e7:79 | 8.11E+10 | 0 | 0 | non-omci |
File2.csv/Sheet2
| CO | E7_20 C.O. | 12(@DHCPatriot) | 16(@100m/50m) | 1776 | Not Used | 1-1-1-101101-GE-1-1 | fixed | none | none | none | none | enabled | ||
| CO | E7_20 C.O. | 12(@DHCPatriot) | 2(@50m/25m) | 1776 | Not Used | 1-1-1-101102-GE-1-1 | derived | none | none | none | none | enabled | ||
| CO | E7_20 C.O. | 4(@DHCP-CGN) | 2(@50m/25m) | 30 | Not Used | 1-1-1-101103-GE-1-1 | derived | none | none | none | none | enabled | ||
| CO | E7_20 C.O. | 12(@DHCPatriot) | 2(@50m/25m) | 1776 | Not Used | 1-1-1-101104-GE-1-1 | fixed | none | none | none | none | enabled | ||
| CO | E7_20 C.O. | 12(@DHCPatriot) | 16(@100m/50m) | 1776 | Not Used | 1-1-1-101105-GE-1-1 | derived | none | none | none | none | enabled |
File3.csv/Sheet3
| CO | E7_20 C.O. | 101101-GE-1 | enabled-no-alarms | 000001788 Reeks 100/50m | 1 | full | 1(system-default) | N | N | N | none | none | uni | DscpMap:1(access) | 1-1-101101 | auto | medium | N | ||||
| CO | E7_20 C.O. | 101102-GE-1 | enabled-no-alarms | 000000163 Shilak 50/25m | 1 | full | 1(system-default) | Y | N | N | none | none | uni | DscpMap:1(access) | 1-1-101102 | auto | medium | N | ||||
| CO | E7_20 C.O. | 101103-GE-1 | enabled-no-alarms | 000009727 Stalker 50/25m | 1 | full | 1(system-default) | Y | N | N | none | none | uni | DscpMap:1(access) | 1-1-101103 | auto | medium | N | ||||
| CO | E7_20 C.O. | 101103-GE-2 | enabled-no-alarms | 1 | full | 1(system-default) | Y | N | N | none | none | uni | DscpMap:1(access) | 1-1-101103 | auto | medium | N | |||||
| CO | E7_20 C.O. | 101104-GE-1 | enabled-no-alarms | 000002552 Fetcher 50/25m | 1 | full | 1(system-default) | N | N | N | none | none | uni | DscpMap:1(access) | 1-1-101104 | auto | medium | N |
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.
