agrazh · May 22, 2025 21:28
diff --git a/excel-comparison-three.html b/excel-comparison-three.html
 <!DOCTYPE html>
 <html>
 <head>
    <title>Excel Advanced Comparison Tool</title>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 20px;
            line-height: 1.6;
        }
        h1, h2, h3 {
            color: #2c3e50;
        }
        .container {
            max-width: 900px;
            margin: 0 auto;
            padding: 0 15px;
        }
        .file-section {
            margin-bottom: 30px;
            padding: 15px;
            background-color: #f8f9fa;
            border-radius: 5px;
            border: 1px solid #ddd;
        }
        .file-input {
            margin-bottom: 15px;
        }
        .column-inputs {
            padding: 10px;
            background-color: #edf2f7;
            border-radius: 4px;
            margin-top: 10px;
        }
        .column-input {
            margin-bottom: 10px;
        }
        label {
            display: block;
            margin-bottom: 5px;
            font-weight: bold;
        }
        input[type="text"] {
            width: 100%;
            padding: 8px;
            border: 1px solid #cbd5e0;
            border-radius: 4px;
            box-sizing: border-box;
        }
        button {
            background-color: #3498db;
            color: white;
            border: none;
            padding: 10px 15px;
            border-radius: 4px;
            cursor: pointer;
            font-size: 16px;
            margin-top: 10px;
        }
        button:hover {
            background-color: #2980b9;
        }
        button:disabled {
            background-color: #a0aec0;
            cursor: not-allowed;
        }
        #results {
            margin-top: 20px;
            display: none;
        }
        .result-section {
            margin-top: 20px;
            padding: 15px;
            border: 1px solid #ddd;
            border-radius: 5px;
            background-color: #f9f9f9;
        }
        .loading {
            display: none;
            margin-top: 20px;
            text-align: center;
            font-weight: bold;
            color: #3498db;
        }
        table {
            border-collapse: collapse;
            width: 100%;
            margin-top: 15px;
            margin-bottom: 15px;
        }
        table, th, td {
            border: 1px solid #ddd;
        }
        th, td {
            padding: 8px;
            text-align: left;
        }
        th {
            background-color: #f2f2f2;
            position: sticky;
            top: 0;
        }
        .table-container {
            max-height: 400px;
            overflow-y: auto;
            margin-top: 15px;
            margin-bottom: 15px;
            border: 1px solid #ddd;
        }
        .summary {
            margin-top: 20px;
            font-weight: bold;
        }
        .status {
            padding: 10px;
            margin-top: 10px;
            border-radius: 4px;
        }
        .status-success {
            background-color: #c6f6d5;
            color: #22543d;
        }
        .status-warning {
            background-color: #fefcbf;
            color: #744210;
        }
        .status-error {
            background-color: #fed7d7;
            color: #822727;
        }
        .tabs {
            display: flex;
            margin-bottom: 15px;
            border-bottom: 2px solid #e2e8f0;
        }
        .tab {
            padding: 10px 15px;
            cursor: pointer;
            margin-right: 5px;
        }
        .tab.active {
            border-bottom: 2px solid #3498db;
            font-weight: bold;
            color: #3498db;
        }
        .tab-content {
            display: none;
        }
        .tab-content.active {
            display: block;
        }
        .export-btn {
            background-color: #38a169;
            margin-left: 10px;
        }
        .export-btn:hover {
            background-color: #2f855a;
        }
        .collapsible {
            background-color: #f2f2f2;
            color: #444;
            cursor: pointer;
            padding: 10px;
            width: 100%;
            border: none;
            text-align: left;
            outline: none;
            font-size: 15px;
            margin-bottom: 5px;
            border-radius: 4px;
        }
        .collapsible:after {
            content: '\002B'; /* Unicode for + sign */
            font-weight: bold;
            float: right;
            margin-left: 5px;
        }
        .active-section:after {
            content: "\2212"; /* Unicode for - sign */
        }
        .collapsible-content {
            max-height: 0;
            overflow: hidden;
            transition: max-height 0.2s ease-out;
            background-color: #f8f9fa;
            border-radius: 0 0 4px 4px;
        }
        .collapsible-inner {
            padding: 0 15px 15px 15px;
        }
    </style>
 </head>
 <body>
    <div class="container">
        <h1>Excel Advanced Comparison Tool</h1>
        
        <button type="button" class="collapsible">Instructions</button>
        <div class="collapsible-content">
            <div class="collapsible-inner">
                <h3>This tool performs the following:</h3>
                <ol>
                    <li>Loads three Excel files:
                        <ul>
                            <li>File 1: Contains "Campaign" and "Final URL" columns</li>
                            <li>File 2: Contains "Campaign ID" and "Final URL" columns</li>
                            <li>Campaign Map: Maps between "Campaign ID" and "Campaign" names</li>
                        </ul>
                    </li>
                    <li>Joins File 1 with Campaign Map using the "Campaign" field</li>
                    <li>Compares the joined data with File 2 to find:
                        <ul>
                            <li>Pairs from File 1 (+ Campaign ID) that exist in File 2</li>
                            <li>Pairs from File 1 (+ Campaign ID) that don't exist in File 2</li>
                        </ul>
                    </li>
                </ol>
                <p>You can specify custom column names for each file if your Excel files use different naming conventions.</p>
            </div>
        </div>
        
        <!-- File 1 Section -->
        <div class="file-section">
            <h2>File 1: pepared by agent</h2>
            <p>This file contains campaign names and URLs.</p>
            
            <div class="file-input">
                <label for="file1">Select File 1:</label>
                <input type="file" id="file1" accept=".xlsx, .xls">
            </div>
            
            <div class="column-inputs">
                <h3>Column Names in File 1</h3>
                <div class="column-input">
                    <label for="file1CampaignCol">Campaign Column:</label>
                    <input type="text" id="file1CampaignCol" value="Campaign" placeholder="e.g. Campaign, Campaign Name">
                </div>
                <div class="column-input">
                    <label for="file1UrlCol">URL Column:</label>
                    <input type="text" id="file1UrlCol" value="Final URL" placeholder="e.g. Final URL, URL, Link">
                </div>
            </div>
        </div>
        
        <!-- File 2 Section -->
        <div class="file-section">
            <h2>File 2: Sitelinks</h2>
            <p>This file contains campaign IDs and URLs.</p>
            
            <div class="file-input">
                <label for="file2">Select File 2:</label>
                <input type="file" id="file2" accept=".xlsx, .xls">
            </div>
            
            <div class="column-inputs">
                <h3>Column Names in File 2</h3>
                <div class="column-input">
                    <label for="file2CampaignIdCol">Campaign ID Column:</label>
                    <input type="text" id="file2CampaignIdCol" value="Campaign ID" placeholder="e.g. Campaign ID, ID">
                </div>
                <div class="column-input">
                    <label for="file2UrlCol">URL Column:</label>
                    <input type="text" id="file2UrlCol" value="Final URL" placeholder="e.g. Final URL, URL, Link">
                </div>
            </div>
        </div>
        
        <!-- Campaign Map Section -->
        <div class="file-section">
            <h2>Campaign Map File: Campaign IDs and Names</h2>
            <p>This file maps between campaign IDs and campaign names.</p>
            
            <div class="file-input">
                <label for="mapFile">Select Campaign Map File:</label>
                <input type="file" id="mapFile" accept=".xlsx, .xls">
            </div>
            
            <div class="column-inputs">
                <h3>Column Names in Campaign Map</h3>
                <div class="column-input">
                    <label for="mapCampaignIdCol">Campaign ID Column:</label>
                    <input type="text" id="mapCampaignIdCol" value="Campaign ID" placeholder="e.g. Campaign ID, ID">
                </div>
                <div class="column-input">
                    <label for="mapCampaignCol">Campaign Column:</label>
                    <input type="text" id="mapCampaignCol" value="Campaign" placeholder="e.g. Campaign, Campaign Name">
                </div>
            </div>
        </div>
        
        <button id="compareBtn">Process Files</button>
        
        <div id="loading" class="loading">Processing files, please wait... This may take a moment for large files.</div>
        
        <div id="results">
            <div class="tabs">
                <div class="tab active" data-tab="foundTab">Found in File 2</div>
                <div class="tab" data-tab="notFoundTab">Not Found in File 2</div>
                <div class="tab" data-tab="summaryTab">Summary</div>
            </div>
            
            <div id="foundTab" class="tab-content active">
                <h2>Pairs from File 1 that were found in File 2</h2>
                <p>These (Campaign, Final URL) pairs from File 1 with their Campaign IDs were found in File 2.</p>
                <div id="foundCount" class="status status-warning"></div>
                <div class="table-container">
                    <table id="foundTable">
                        <thead>
                            <tr>
                                <th>#</th>
                                <th>Campaign ID</th>
                                <th>Campaign</th>
                                <th>Final URL</th>
                            </tr>
                        </thead>
                        <tbody id="foundTableBody"></tbody>
                    </table>
                </div>
                <button id="exportFoundBtn" class="export-btn">Export to Excel</button>
            </div>
            
            <div id="notFoundTab" class="tab-content">
                <h2>Pairs from File 1 that were NOT found in File 2</h2>
                <p>These (Campaign, Final URL) pairs from File 1 with their Campaign IDs were not found in File 2.</p>
                <div id="notFoundCount" class="status status-success"></div>
                <div class="table-container">
                    <table id="notFoundTable">
                        <thead>
                            <tr>
                                <th>#</th>
                                <th>Campaign ID</th>
                                <th>Campaign</th>
                                <th>Final URL</th>
                            </tr>
                        </thead>
                        <tbody id="notFoundTableBody"></tbody>
                    </table>
                </div>
                <button id="exportNotFoundBtn" class="export-btn">Export to Excel</button>
            </div>
            
            <div id="summaryTab" class="tab-content">
                <h2>Processing Summary</h2>
                <div id="summary" class="result-section"></div>
            </div>
        </div>
    </div>

    <script>
        // Collapsible sections
        const coll = document.getElementsByClassName("collapsible");
        for (let i = 0; i < coll.length; i++) {
            coll[i].addEventListener("click", function() {
                this.classList.toggle("active-section");
                const content = this.nextElementSibling;
                if (content.style.maxHeight) {
                    content.style.maxHeight = null;
                } else {
                    content.style.maxHeight = content.scrollHeight + "px";
                }
            });
        }
        
        // Tab functionality
        document.querySelectorAll('.tab').forEach(tab => {
            tab.addEventListener('click', () => {
                // Remove active class from all tabs
                document.querySelectorAll('.tab').forEach(t => t.classList.remove('active'));
                document.querySelectorAll('.tab-content').forEach(c => c.classList.remove('active'));
                
                // Add active class to clicked tab
                tab.classList.add('active');
                document.getElementById(tab.dataset.tab).classList.add('active');
            });
        });
        
        // Main process button
        document.getElementById('compareBtn').addEventListener('click', processFiles);
        
        // Export buttons
        document.getElementById('exportFoundBtn').addEventListener('click', () => {
            exportToExcel('foundTable', 'Found_Pairs');
        });
        
        document.getElementById('exportNotFoundBtn').addEventListener('click', () => {
            exportToExcel('notFoundTable', 'Not_Found_Pairs');
        });
        
        // Function to process all files
        async function processFiles() {
            const file1Input = document.getElementById('file1');
            const file2Input = document.getElementById('file2');
            const mapFileInput = document.getElementById('mapFile');
            const loadingDiv = document.getElementById('loading');
            const resultsDiv = document.getElementById('results');
            
            // Get column names from inputs
            const file1CampaignCol = document.getElementById('file1CampaignCol').value.trim();
            const file1UrlCol = document.getElementById('file1UrlCol').value.trim();
            const file2CampaignIdCol = document.getElementById('file2CampaignIdCol').value.trim();
            const file2UrlCol = document.getElementById('file2UrlCol').value.trim();
            const mapCampaignIdCol = document.getElementById('mapCampaignIdCol').value.trim();
            const mapCampaignCol = document.getElementById('mapCampaignCol').value.trim();
            
            // Validate inputs
            if (!file1CampaignCol || !file1UrlCol || !file2CampaignIdCol || !file2UrlCol || 
                !mapCampaignIdCol || !mapCampaignCol) {
                alert('Please specify all column names');
                return;
            }
            
            // Check if all files are selected
            if (!file1Input.files[0] || !file2Input.files[0] || !mapFileInput.files[0]) {
                alert('Please select all three Excel files');
                return;
            }
            
            // Show loading message
            loadingDiv.style.display = 'block';
            resultsDiv.style.display = 'none';
            
            try {
                // Read all files
                const file1Data = await readExcelFile(file1Input.files[0]);
                const file2Data = await readExcelFile(file2Input.files[0]);
                const mapFileData = await readExcelFile(mapFileInput.files[0]);
                
                // Process the data
                const result = processData(
                    file1Data, file2Data, mapFileData,
                    file1CampaignCol, file1UrlCol,
                    file2CampaignIdCol, file2UrlCol,
                    mapCampaignIdCol, mapCampaignCol
                );
                
                // Display results
                displayResults(result);
                
                // Hide loading and show results
                loadingDiv.style.display = 'none';
                resultsDiv.style.display = 'block';
                
            } catch (error) {
                loadingDiv.style.display = 'none';
                alert('Error: ' + error.message);
                console.error('Error during processing:', error);
            }
        }
        
        // Function to read Excel file
        async function readExcelFile(file) {
            return new Promise((resolve, reject) => {
                const reader = new FileReader();
                
                reader.onload = function(e) {
                    try {
                        const data = new Uint8Array(e.target.result);
                        
                        // Parse Excel file
                        const workbook = XLSX.read(data, {
                            type: 'array',
                            cellStyles: true,
                            cellFormulas: true,
                            cellDates: true,
                            cellNF: true,
                            sheetStubs: true
                        });
                        
                        // Get the first sheet
                        const firstSheetName = workbook.SheetNames[0];
                        const worksheet = workbook.Sheets[firstSheetName];
                        
                        // Convert to JSON
                        const jsonData = XLSX.utils.sheet_to_json(worksheet);
                        
                        resolve(jsonData);
                    } catch (error) {
                        reject(error);
                    }
                };
                
                reader.onerror = function() {
                    reject(new Error('Error reading file'));
                };
                
                reader.readAsArrayBuffer(file);
            });
        }
        
        // Function to process the data from all files
        function processData(
            file1Data, file2Data, mapFileData,
            file1CampaignCol, file1UrlCol,
            file2CampaignIdCol, file2UrlCol,
            mapCampaignIdCol, mapCampaignCol
        ) {
            // Normalize column names (find the actual case-sensitive names)
            const file1Cols = normalizeColumns(file1Data[0], {
                campaign: file1CampaignCol,
                url: file1UrlCol
            });
            
            const file2Cols = normalizeColumns(file2Data[0], {
                campaignId: file2CampaignIdCol,
                url: file2UrlCol
            });
            
            const mapCols = normalizeColumns(mapFileData[0], {
                campaignId: mapCampaignIdCol,
                campaign: mapCampaignCol
            });
            
            // Create a mapping from Campaign to Campaign ID
            const campaignToIdMap = {};
            mapFileData.forEach(row => {
                if (row[mapCols.campaign] && row[mapCols.campaignId]) {
                    campaignToIdMap[row[mapCols.campaign]] = row[mapCols.campaignId];
                }
            });
            
            // Create a set of pairs from file2 for efficient lookup
            const file2Pairs = new Set();
            file2Data.forEach(row => {
                if (row[file2Cols.campaignId] && row[file2Cols.url]) {
                    const idUrlPair = `${row[file2Cols.campaignId]}|||${row[file2Cols.url]}`;
                    file2Pairs.add(idUrlPair);
                }
            });
            
            // Process file1 and join with campaign map
            const joinedData = [];
            let unmappedCampaigns = 0;
            
            file1Data.forEach(row => {
                if (row[file1Cols.campaign] && row[file1Cols.url]) {
                    const campaignName = row[file1Cols.campaign];
                    const url = row[file1Cols.url];
                    const campaignId = campaignToIdMap[campaignName];
                    
                    if (campaignId) {
                        joinedData.push({
                            campaignId: campaignId,
                            campaign: campaignName,
                            url: url
                        });
                    } else {
                        unmappedCampaigns++;
                    }
                }
            });
            
            // Check which pairs exist in file2
            const foundInFile2 = [];
            const notFoundInFile2 = [];
            
            joinedData.forEach(item => {
                const idUrlPair = `${item.campaignId}|||${item.url}`;
                
                if (file2Pairs.has(idUrlPair)) {
                    foundInFile2.push(item);
                } else {
                    notFoundInFile2.push(item);
                }
            });
            
            return {
                foundInFile2,
                notFoundInFile2,
                stats: {
                    file1Count: file1Data.length,
                    file2Count: file2Data.length,
                    mapFileCount: mapFileData.length,
                    joinedCount: joinedData.length,
                    unmappedCampaigns,
                    foundCount: foundInFile2.length,
                    notFoundCount: notFoundInFile2.length
                }
            };
        }
        
        // Helper function to normalize column names (handle case sensitivity)
        function normalizeColumns(firstRow, columnsToFind) {
            const result = {};
            const keys = Object.keys(firstRow);
            
            for (const [key, value] of Object.entries(columnsToFind)) {
                const valueToFind = value.toLowerCase();
                let found = false;
                
                for (const rowKey of keys) {
                    if (rowKey.toLowerCase() === valueToFind) {
                        result[key] = rowKey;
                        found = true;
                        break;
                    }
                }
                
                if (!found) {
                    throw new Error(`Column '${value}' not found. Available columns: ${keys.join(', ')}`);
                }
            }
            
            return result;
        }
        
        // Function to display results
        function displayResults(result) {
            // Found in File 2
            const foundTableBody = document.getElementById('foundTableBody');
            foundTableBody.innerHTML = '';
            
            result.foundInFile2.forEach((item, index) => {
                const row = document.createElement('tr');
                row.innerHTML = `
                    <td>${index + 1}</td>
                    <td>${escapeHTML(item.campaignId)}</td>
                    <td>${escapeHTML(item.campaign)}</td>
                    <td>${escapeHTML(item.url)}</td>
                `;
                foundTableBody.appendChild(row);
            });
            
            // Not Found in File 2
            const notFoundTableBody = document.getElementById('notFoundTableBody');
            notFoundTableBody.innerHTML = '';
            
            result.notFoundInFile2.forEach((item, index) => {
                const row = document.createElement('tr');
                row.innerHTML = `
                    <td>${index + 1}</td>
                    <td>${escapeHTML(item.campaignId)}</td>
                    <td>${escapeHTML(item.campaign)}</td>
                    <td>${escapeHTML(item.url)}</td>
                `;
                notFoundTableBody.appendChild(row);
            });
            
            // Summary
            const summaryDiv = document.getElementById('summary');
            summaryDiv.innerHTML = `
                <p><strong>File 1:</strong> ${result.stats.file1Count} rows</p>
                <p><strong>File 2:</strong> ${result.stats.file2Count} rows</p>
                <p><strong>Campaign Map:</strong> ${result.stats.mapFileCount} rows</p>
                <p><strong>Joined Data:</strong> ${result.stats.joinedCount} rows</p>
                <p><strong>Unmapped Campaigns:</strong> ${result.stats.unmappedCampaigns} (campaigns from File 1 not found in Campaign Map)</p>
                <p><strong>Found in File 2:</strong> ${result.stats.foundCount} pairs</p>
                <p><strong>Not Found in File 2:</strong> ${result.stats.notFoundCount} pairs</p>
            `;
            
            // Status counts
            document.getElementById('foundCount').innerHTML = `Found ${result.stats.foundCount} pairs in File 2`;
            document.getElementById('notFoundCount').innerHTML = `Found ${result.stats.notFoundCount} pairs that are unique to File 1`;
            
            // Update status colors
            if (result.stats.foundCount > 0) {
                document.getElementById('foundCount').className = 'status status-warning';
            } else {
                document.getElementById('foundCount').className = 'status status-success';
            }
        }
        
        // Function to safely escape HTML
        function escapeHTML(str) {
            if (!str && str !== 0) return '';
            return String(str)
                .replace(/&/g, '&amp;')
                .replace(/</g, '&lt;')
                .replace(/>/g, '&gt;')
                .replace(/"/g, '&quot;')
                .replace(/'/g, '&#039;');
        }
        
        // Function to export table to Excel
        function exportToExcel(tableId, fileName) {
            const table = document.getElementById(tableId);
            const ws = XLSX.utils.table_to_sheet(table);
            const wb = XLSX.utils.book_new();
            XLSX.utils.book_append_sheet(wb, ws, 'Data');
            XLSX.writeFile(wb, `${fileName}.xlsx`);
        }
    </script>
 </body>
 </html>
	<!DOCTYPE html>
	<html>
	<head>
	<title>Excel Advanced Comparison Tool</title>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
	<style>
	body {
	font-family: Arial, sans-serif;
	margin: 20px;
	line-height: 1.6;
	}
	h1, h2, h3 {
	color: #2c3e50;
	}
	.container {
	max-width: 900px;
	margin: 0 auto;
	padding: 0 15px;
	}
	.file-section {
	margin-bottom: 30px;
	padding: 15px;
	background-color: #f8f9fa;
	border-radius: 5px;
	border: 1px solid #ddd;
	}
	.file-input {
	margin-bottom: 15px;
	}
	.column-inputs {
	padding: 10px;
	background-color: #edf2f7;
	border-radius: 4px;
	margin-top: 10px;
	}
	.column-input {
	margin-bottom: 10px;
	}
	label {
	display: block;
	margin-bottom: 5px;
	font-weight: bold;
	}
	input[type="text"] {
	width: 100%;
	padding: 8px;
	border: 1px solid #cbd5e0;
	border-radius: 4px;
	box-sizing: border-box;
	}
	button {
	background-color: #3498db;
	color: white;
	border: none;
	padding: 10px 15px;
	border-radius: 4px;
	cursor: pointer;
	font-size: 16px;
	margin-top: 10px;
	}
	button:hover {
	background-color: #2980b9;
	}
	button:disabled {
	background-color: #a0aec0;
	cursor: not-allowed;
	}
	#results {
	margin-top: 20px;
	display: none;
	}
	.result-section {
	margin-top: 20px;
	padding: 15px;
	border: 1px solid #ddd;
	border-radius: 5px;
	background-color: #f9f9f9;
	}
	.loading {
	display: none;
	margin-top: 20px;
	text-align: center;
	font-weight: bold;
	color: #3498db;
	}
	table {
	border-collapse: collapse;
	width: 100%;
	margin-top: 15px;
	margin-bottom: 15px;
	}
	table, th, td {
	border: 1px solid #ddd;
	}
	th, td {
	padding: 8px;
	text-align: left;
	}
	th {
	background-color: #f2f2f2;
	position: sticky;
	top: 0;
	}
	.table-container {
	max-height: 400px;
	overflow-y: auto;
	margin-top: 15px;
	margin-bottom: 15px;
	border: 1px solid #ddd;
	}
	.summary {
	margin-top: 20px;
	font-weight: bold;
	}
	.status {
	padding: 10px;
	margin-top: 10px;
	border-radius: 4px;
	}
	.status-success {
	background-color: #c6f6d5;
	color: #22543d;
	}
	.status-warning {
	background-color: #fefcbf;
	color: #744210;
	}
	.status-error {
	background-color: #fed7d7;
	color: #822727;
	}
	.tabs {
	display: flex;
	margin-bottom: 15px;
	border-bottom: 2px solid #e2e8f0;
	}
	.tab {
	padding: 10px 15px;
	cursor: pointer;
	margin-right: 5px;
	}
	.tab.active {
	border-bottom: 2px solid #3498db;
	font-weight: bold;
	color: #3498db;
	}
	.tab-content {
	display: none;
	}
	.tab-content.active {
	display: block;
	}
	.export-btn {
	background-color: #38a169;
	margin-left: 10px;
	}
	.export-btn:hover {
	background-color: #2f855a;
	}
	.collapsible {
	background-color: #f2f2f2;
	color: #444;
	cursor: pointer;
	padding: 10px;
	width: 100%;
	border: none;
	text-align: left;
	outline: none;
	font-size: 15px;
	margin-bottom: 5px;
	border-radius: 4px;
	}
	.collapsible:after {
	content: '\002B'; /* Unicode for + sign */
	font-weight: bold;
	float: right;
	margin-left: 5px;
	}
	.active-section:after {
	content: "\2212"; /* Unicode for - sign */
	}
	.collapsible-content {
	max-height: 0;
	overflow: hidden;
	transition: max-height 0.2s ease-out;
	background-color: #f8f9fa;
	border-radius: 0 0 4px 4px;
	}
	.collapsible-inner {
	padding: 0 15px 15px 15px;
	}
	</style>
	</head>
	<body>
	<div class="container">
	<h1>Excel Advanced Comparison Tool</h1>

	<button type="button" class="collapsible">Instructions</button>
	<div class="collapsible-content">
	<div class="collapsible-inner">
	<h3>This tool performs the following:</h3>
	<ol>
	<li>Loads three Excel files:
	<ul>
	<li>File 1: Contains "Campaign" and "Final URL" columns</li>
	<li>File 2: Contains "Campaign ID" and "Final URL" columns</li>
	<li>Campaign Map: Maps between "Campaign ID" and "Campaign" names</li>
	</ul>
	</li>
	<li>Joins File 1 with Campaign Map using the "Campaign" field</li>
	<li>Compares the joined data with File 2 to find:
	<ul>
	<li>Pairs from File 1 (+ Campaign ID) that exist in File 2</li>
	<li>Pairs from File 1 (+ Campaign ID) that don't exist in File 2</li>
	</ul>
	</li>
	</ol>
	<p>You can specify custom column names for each file if your Excel files use different naming conventions.</p>
	</div>
	</div>

	<!-- File 1 Section -->
	<div class="file-section">
	<h2>File 1: pepared by agent</h2>
	<p>This file contains campaign names and URLs.</p>

	<div class="file-input">
	<label for="file1">Select File 1:</label>
	<input type="file" id="file1" accept=".xlsx, .xls">
	</div>

	<div class="column-inputs">
	<h3>Column Names in File 1</h3>
	<div class="column-input">
	<label for="file1CampaignCol">Campaign Column:</label>
	<input type="text" id="file1CampaignCol" value="Campaign" placeholder="e.g. Campaign, Campaign Name">
	</div>
	<div class="column-input">
	<label for="file1UrlCol">URL Column:</label>
	<input type="text" id="file1UrlCol" value="Final URL" placeholder="e.g. Final URL, URL, Link">
	</div>
	</div>
	</div>

	<!-- File 2 Section -->
	<div class="file-section">
	<h2>File 2: Sitelinks</h2>
	<p>This file contains campaign IDs and URLs.</p>

	<div class="file-input">
	<label for="file2">Select File 2:</label>
	<input type="file" id="file2" accept=".xlsx, .xls">
	</div>

	<div class="column-inputs">
	<h3>Column Names in File 2</h3>
	<div class="column-input">
	<label for="file2CampaignIdCol">Campaign ID Column:</label>
	<input type="text" id="file2CampaignIdCol" value="Campaign ID" placeholder="e.g. Campaign ID, ID">
	</div>
	<div class="column-input">
	<label for="file2UrlCol">URL Column:</label>
	<input type="text" id="file2UrlCol" value="Final URL" placeholder="e.g. Final URL, URL, Link">
	</div>
	</div>
	</div>

	<!-- Campaign Map Section -->
	<div class="file-section">
	<h2>Campaign Map File: Campaign IDs and Names</h2>
	<p>This file maps between campaign IDs and campaign names.</p>

	<div class="file-input">
	<label for="mapFile">Select Campaign Map File:</label>
	<input type="file" id="mapFile" accept=".xlsx, .xls">
	</div>

	<div class="column-inputs">
	<h3>Column Names in Campaign Map</h3>
	<div class="column-input">
	<label for="mapCampaignIdCol">Campaign ID Column:</label>
	<input type="text" id="mapCampaignIdCol" value="Campaign ID" placeholder="e.g. Campaign ID, ID">
	</div>
	<div class="column-input">
	<label for="mapCampaignCol">Campaign Column:</label>
	<input type="text" id="mapCampaignCol" value="Campaign" placeholder="e.g. Campaign, Campaign Name">
	</div>
	</div>
	</div>

	<button id="compareBtn">Process Files</button>

	<div id="loading" class="loading">Processing files, please wait... This may take a moment for large files.</div>

	<div id="results">
	<div class="tabs">
	<div class="tab active" data-tab="foundTab">Found in File 2</div>
	<div class="tab" data-tab="notFoundTab">Not Found in File 2</div>
	<div class="tab" data-tab="summaryTab">Summary</div>
	</div>

	<div id="foundTab" class="tab-content active">
	<h2>Pairs from File 1 that were found in File 2</h2>
	<p>These (Campaign, Final URL) pairs from File 1 with their Campaign IDs were found in File 2.</p>
	<div id="foundCount" class="status status-warning"></div>
	<div class="table-container">
	<table id="foundTable">
	<thead>
	<tr>
	<th>#</th>
	<th>Campaign ID</th>
	<th>Campaign</th>
	<th>Final URL</th>
	</tr>
	</thead>
	<tbody id="foundTableBody"></tbody>
	</table>
	</div>
	<button id="exportFoundBtn" class="export-btn">Export to Excel</button>
	</div>

	<div id="notFoundTab" class="tab-content">
	<h2>Pairs from File 1 that were NOT found in File 2</h2>
	<p>These (Campaign, Final URL) pairs from File 1 with their Campaign IDs were not found in File 2.</p>
	<div id="notFoundCount" class="status status-success"></div>
	<div class="table-container">
	<table id="notFoundTable">
	<thead>
	<tr>
	<th>#</th>
	<th>Campaign ID</th>
	<th>Campaign</th>
	<th>Final URL</th>
	</tr>
	</thead>
	<tbody id="notFoundTableBody"></tbody>
	</table>
	</div>
	<button id="exportNotFoundBtn" class="export-btn">Export to Excel</button>
	</div>

	<div id="summaryTab" class="tab-content">
	<h2>Processing Summary</h2>
	<div id="summary" class="result-section"></div>
	</div>
	</div>
	</div>

	<script>
	// Collapsible sections
	const coll = document.getElementsByClassName("collapsible");
	for (let i = 0; i < coll.length; i++) {
	coll[i].addEventListener("click", function() {
	this.classList.toggle("active-section");
	const content = this.nextElementSibling;
	if (content.style.maxHeight) {
	content.style.maxHeight = null;
	} else {
	content.style.maxHeight = content.scrollHeight + "px";
	}
	});
	}

	// Tab functionality
	document.querySelectorAll('.tab').forEach(tab => {
	tab.addEventListener('click', () => {
	// Remove active class from all tabs
	document.querySelectorAll('.tab').forEach(t => t.classList.remove('active'));
	document.querySelectorAll('.tab-content').forEach(c => c.classList.remove('active'));

	// Add active class to clicked tab
	tab.classList.add('active');
	document.getElementById(tab.dataset.tab).classList.add('active');
	});
	});

	// Main process button
	document.getElementById('compareBtn').addEventListener('click', processFiles);

	// Export buttons
	document.getElementById('exportFoundBtn').addEventListener('click', () => {
	exportToExcel('foundTable', 'Found_Pairs');
	});

	document.getElementById('exportNotFoundBtn').addEventListener('click', () => {
	exportToExcel('notFoundTable', 'Not_Found_Pairs');
	});

	// Function to process all files
	async function processFiles() {
	const file1Input = document.getElementById('file1');
	const file2Input = document.getElementById('file2');
	const mapFileInput = document.getElementById('mapFile');
	const loadingDiv = document.getElementById('loading');
	const resultsDiv = document.getElementById('results');

	// Get column names from inputs
	const file1CampaignCol = document.getElementById('file1CampaignCol').value.trim();
	const file1UrlCol = document.getElementById('file1UrlCol').value.trim();
	const file2CampaignIdCol = document.getElementById('file2CampaignIdCol').value.trim();
	const file2UrlCol = document.getElementById('file2UrlCol').value.trim();
	const mapCampaignIdCol = document.getElementById('mapCampaignIdCol').value.trim();
	const mapCampaignCol = document.getElementById('mapCampaignCol').value.trim();

	// Validate inputs
	if (!file1CampaignCol \|\| !file1UrlCol \|\| !file2CampaignIdCol \|\| !file2UrlCol \|\|
	!mapCampaignIdCol \|\| !mapCampaignCol) {
	alert('Please specify all column names');
	return;
	}

	// Check if all files are selected
	if (!file1Input.files[0] \|\| !file2Input.files[0] \|\| !mapFileInput.files[0]) {
	alert('Please select all three Excel files');
	return;
	}

	// Show loading message
	loadingDiv.style.display = 'block';
	resultsDiv.style.display = 'none';

	try {
	// Read all files
	const file1Data = await readExcelFile(file1Input.files[0]);
	const file2Data = await readExcelFile(file2Input.files[0]);
	const mapFileData = await readExcelFile(mapFileInput.files[0]);

	// Process the data
	const result = processData(
	file1Data, file2Data, mapFileData,
	file1CampaignCol, file1UrlCol,
	file2CampaignIdCol, file2UrlCol,
	mapCampaignIdCol, mapCampaignCol
	);

	// Display results
	displayResults(result);

	// Hide loading and show results
	loadingDiv.style.display = 'none';
	resultsDiv.style.display = 'block';

	} catch (error) {
	loadingDiv.style.display = 'none';
	alert('Error: ' + error.message);
	console.error('Error during processing:', error);
	}
	}

	// Function to read Excel file
	async function readExcelFile(file) {
	return new Promise((resolve, reject) => {
	const reader = new FileReader();

	reader.onload = function(e) {
	try {
	const data = new Uint8Array(e.target.result);

	// Parse Excel file
	const workbook = XLSX.read(data, {
	type: 'array',
	cellStyles: true,
	cellFormulas: true,
	cellDates: true,
	cellNF: true,
	sheetStubs: true
	});

	// Get the first sheet
	const firstSheetName = workbook.SheetNames[0];
	const worksheet = workbook.Sheets[firstSheetName];

	// Convert to JSON
	const jsonData = XLSX.utils.sheet_to_json(worksheet);

	resolve(jsonData);
	} catch (error) {
	reject(error);
	}
	};

	reader.onerror = function() {
	reject(new Error('Error reading file'));
	};

	reader.readAsArrayBuffer(file);
	});
	}

	// Function to process the data from all files
	function processData(
	file1Data, file2Data, mapFileData,
	file1CampaignCol, file1UrlCol,
	file2CampaignIdCol, file2UrlCol,
	mapCampaignIdCol, mapCampaignCol
	) {
	// Normalize column names (find the actual case-sensitive names)
	const file1Cols = normalizeColumns(file1Data[0], {
	campaign: file1CampaignCol,
	url: file1UrlCol
	});

	const file2Cols = normalizeColumns(file2Data[0], {
	campaignId: file2CampaignIdCol,
	url: file2UrlCol
	});

	const mapCols = normalizeColumns(mapFileData[0], {
	campaignId: mapCampaignIdCol,
	campaign: mapCampaignCol
	});

	// Create a mapping from Campaign to Campaign ID
	const campaignToIdMap = {};
	mapFileData.forEach(row => {
	if (row[mapCols.campaign] && row[mapCols.campaignId]) {
	campaignToIdMap[row[mapCols.campaign]] = row[mapCols.campaignId];
	}
	});

	// Create a set of pairs from file2 for efficient lookup
	const file2Pairs = new Set();
	file2Data.forEach(row => {
	if (row[file2Cols.campaignId] && row[file2Cols.url]) {
	const idUrlPair = `${row[file2Cols.campaignId]}\|\|\|${row[file2Cols.url]}`;
	file2Pairs.add(idUrlPair);
	}
	});

	// Process file1 and join with campaign map
	const joinedData = [];
	let unmappedCampaigns = 0;

	file1Data.forEach(row => {
	if (row[file1Cols.campaign] && row[file1Cols.url]) {
	const campaignName = row[file1Cols.campaign];
	const url = row[file1Cols.url];
	const campaignId = campaignToIdMap[campaignName];

	if (campaignId) {
	joinedData.push({
	campaignId: campaignId,
	campaign: campaignName,
	url: url
	});
	} else {
	unmappedCampaigns++;
	}
	}
	});

	// Check which pairs exist in file2
	const foundInFile2 = [];
	const notFoundInFile2 = [];

	joinedData.forEach(item => {
	const idUrlPair = `${item.campaignId}\|\|\|${item.url}`;

	if (file2Pairs.has(idUrlPair)) {
	foundInFile2.push(item);
	} else {
	notFoundInFile2.push(item);
	}
	});

	return {
	foundInFile2,
	notFoundInFile2,
	stats: {
	file1Count: file1Data.length,
	file2Count: file2Data.length,
	mapFileCount: mapFileData.length,
	joinedCount: joinedData.length,
	unmappedCampaigns,
	foundCount: foundInFile2.length,
	notFoundCount: notFoundInFile2.length
	}
	};
	}

	// Helper function to normalize column names (handle case sensitivity)
	function normalizeColumns(firstRow, columnsToFind) {
	const result = {};
	const keys = Object.keys(firstRow);

	for (const [key, value] of Object.entries(columnsToFind)) {
	const valueToFind = value.toLowerCase();
	let found = false;

	for (const rowKey of keys) {
	if (rowKey.toLowerCase() === valueToFind) {
	result[key] = rowKey;
	found = true;
	break;
	}
	}

	if (!found) {
	throw new Error(`Column '${value}' not found. Available columns: ${keys.join(', ')}`);
	}
	}

	return result;
	}

	// Function to display results
	function displayResults(result) {
	// Found in File 2
	const foundTableBody = document.getElementById('foundTableBody');
	foundTableBody.innerHTML = '';

	result.foundInFile2.forEach((item, index) => {
	const row = document.createElement('tr');
	row.innerHTML = `
	<td>${index + 1}</td>
	<td>${escapeHTML(item.campaignId)}</td>
	<td>${escapeHTML(item.campaign)}</td>
	<td>${escapeHTML(item.url)}</td>
	`;
	foundTableBody.appendChild(row);
	});

	// Not Found in File 2
	const notFoundTableBody = document.getElementById('notFoundTableBody');
	notFoundTableBody.innerHTML = '';

	result.notFoundInFile2.forEach((item, index) => {
	const row = document.createElement('tr');
	row.innerHTML = `
	<td>${index + 1}</td>
	<td>${escapeHTML(item.campaignId)}</td>
	<td>${escapeHTML(item.campaign)}</td>
	<td>${escapeHTML(item.url)}</td>
	`;
	notFoundTableBody.appendChild(row);
	});

	// Summary
	const summaryDiv = document.getElementById('summary');
	summaryDiv.innerHTML = `
	<p><strong>File 1:</strong> ${result.stats.file1Count} rows</p>
	<p><strong>File 2:</strong> ${result.stats.file2Count} rows</p>
	<p><strong>Campaign Map:</strong> ${result.stats.mapFileCount} rows</p>
	<p><strong>Joined Data:</strong> ${result.stats.joinedCount} rows</p>
	<p><strong>Unmapped Campaigns:</strong> ${result.stats.unmappedCampaigns} (campaigns from File 1 not found in Campaign Map)</p>
	<p><strong>Found in File 2:</strong> ${result.stats.foundCount} pairs</p>
	<p><strong>Not Found in File 2:</strong> ${result.stats.notFoundCount} pairs</p>
	`;

	// Status counts
	document.getElementById('foundCount').innerHTML = `Found ${result.stats.foundCount} pairs in File 2`;
	document.getElementById('notFoundCount').innerHTML = `Found ${result.stats.notFoundCount} pairs that are unique to File 1`;

	// Update status colors
	if (result.stats.foundCount > 0) {
	document.getElementById('foundCount').className = 'status status-warning';
	} else {
	document.getElementById('foundCount').className = 'status status-success';
	}
	}

	// Function to safely escape HTML
	function escapeHTML(str) {
	if (!str && str !== 0) return '';
	return String(str)
	.replace(/&/g, '&')
	.replace(/</g, '<')
	.replace(/>/g, '>')
	.replace(/"/g, '"')
	.replace(/'/g, ''');
	}

	// Function to export table to Excel
	function exportToExcel(tableId, fileName) {
	const table = document.getElementById(tableId);
	const ws = XLSX.utils.table_to_sheet(table);
	const wb = XLSX.utils.book_new();
	XLSX.utils.book_append_sheet(wb, ws, 'Data');
	XLSX.writeFile(wb, `${fileName}.xlsx`);
	}
	</script>
	</body>
	</html>