agrazh · May 22, 2025 21:25
diff --git a/excel-compare-two.html b/excel-compare-two.html
 <!DOCTYPE html>
 <html>
 <head>
    <title>Excel Compare 2 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 Compare 2 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 two Excel files:
                        <ul>
                            <li>File 1: Contains "Campaign" and "Suggested Final URL" columns</li>
                            <li>File 2: Contains "Campaign" and "Final URL" columns</li>
                        </ul>
                    </li>
                    <li>Joins both files by the "Campaign" column</li>
                    <li>Compares the joined data to find:
                        <ul>
                            <li>Campaigns where "Suggested Final URL" values appear in "Final URL" values</li>
                            <li>Campaigns where "Suggested Final URL" values don't appear in "Final URL" values</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</h2>
            <p>This file contains campaign names and suggested 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">Suggested Final URL Column:</label>
                    <input type="text" id="file1UrlCol" value="Suggested Final URL" placeholder="e.g. Suggested Final URL, Suggested URL">
                </div>
            </div>
        </div>
        
        <!-- File 2 Section -->
        <div class="file-section">
            <h2>File 2</h2>
            <p>This file contains campaign names and final 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="file2CampaignCol">Campaign Column:</label>
                    <input type="text" id="file2CampaignCol" value="Campaign" placeholder="e.g. Campaign, Campaign Name">
                </div>
                <div class="column-input">
                    <label for="file2UrlCol">Final URL Column:</label>
                    <input type="text" id="file2UrlCol" value="Final URL" placeholder="e.g. Final URL, URL, Link">
                </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>Suggested URLs that were found in File 2</h2>
                <p>These campaigns have "Suggested Final URL" values that appear among "Final URL" values 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</th>
                                <th>Suggested Final URL</th>
                                <th>Matching 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>Suggested URLs that were NOT found in File 2</h2>
                <p>These campaigns have "Suggested Final URL" values that don't appear among "Final URL" values 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</th>
                                <th>Suggested 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_Suggested_URLs');
        });
        
        document.getElementById('exportNotFoundBtn').addEventListener('click', () => {
            exportToExcel('notFoundTable', 'Not_Found_Suggested_URLs');
        });
        
        // Function to process both files
        async function processFiles() {
            const file1Input = document.getElementById('file1');
            const file2Input = document.getElementById('file2');
            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 file2CampaignCol = document.getElementById('file2CampaignCol').value.trim();
            const file2UrlCol = document.getElementById('file2UrlCol').value.trim();
            
            // Validate inputs
            if (!file1CampaignCol || !file1UrlCol || !file2CampaignCol || !file2UrlCol) {
                alert('Please specify all column names');
                return;
            }
            
            // Check if both files are selected
            if (!file1Input.files[0] || !file2Input.files[0]) {
                alert('Please select both Excel files');
                return;
            }
            
            // Show loading message
            loadingDiv.style.display = 'block';
            resultsDiv.style.display = 'none';
            
            try {
                // Read both files
                const file1Data = await readExcelFile(file1Input.files[0]);
                const file2Data = await readExcelFile(file2Input.files[0]);
                
                // Process the data
                const result = processData(
                    file1Data, file2Data,
                    file1CampaignCol, file1UrlCol,
                    file2CampaignCol, file2UrlCol
                );
                
                // 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 both files
        function processData(
            file1Data, file2Data,
            file1CampaignCol, file1UrlCol,
            file2CampaignCol, file2UrlCol
        ) {
            // Normalize column names (find the actual case-sensitive names)
            const file1Cols = normalizeColumns(file1Data[0], {
                campaign: file1CampaignCol,
                url: file1UrlCol
            });
            
            const file2Cols = normalizeColumns(file2Data[0], {
                campaign: file2CampaignCol,
                url: file2UrlCol
            });
            
            // Create a mapping from Campaign to Final URLs in File 2
            const campaignToUrlsMap = {};
            file2Data.forEach(row => {
                if (row[file2Cols.campaign] && row[file2Cols.url]) {
                    const campaign = row[file2Cols.campaign];
                    const url = row[file2Cols.url];
                    
                    if (!campaignToUrlsMap[campaign]) {
                        campaignToUrlsMap[campaign] = new Set();
                    }
                    campaignToUrlsMap[campaign].add(url);
                }
            });
            
            // Process file1 and check against file2
            const foundInFile2 = [];
            const notFoundInFile2 = [];
            let processedRows = 0;
            let skippedRows = 0;
            
            file1Data.forEach(row => {
                if (row[file1Cols.campaign] && row[file1Cols.url]) {
                    const campaign = row[file1Cols.campaign];
                    const suggestedUrl = row[file1Cols.url];
                    
                    processedRows++;
                    
                    // Check if this campaign exists in file2 and if the suggested URL matches any final URL
                    if (campaignToUrlsMap[campaign] && campaignToUrlsMap[campaign].has(suggestedUrl)) {
                        foundInFile2.push({
                            campaign: campaign,
                            suggestedUrl: suggestedUrl,
                            matchingUrl: suggestedUrl  // They're the same in this case
                        });
                    } else {
                        notFoundInFile2.push({
                            campaign: campaign,
                            suggestedUrl: suggestedUrl
                        });
                    }
                } else {
                    skippedRows++;
                }
            });
            
            return {
                foundInFile2,
                notFoundInFile2,
                stats: {
                    file1Count: file1Data.length,
                    file2Count: file2Data.length,
                    processedRows,
                    skippedRows,
                    foundCount: foundInFile2.length,
                    notFoundCount: notFoundInFile2.length,
                    uniqueCampaignsInFile2: Object.keys(campaignToUrlsMap).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.campaign)}</td>
                    <td>${escapeHTML(item.suggestedUrl)}</td>
                    <td>${escapeHTML(item.matchingUrl)}</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.campaign)}</td>
                    <td>${escapeHTML(item.suggestedUrl)}</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>Processed Rows from File 1:</strong> ${result.stats.processedRows} rows</p>
                <p><strong>Skipped Rows (missing data):</strong> ${result.stats.skippedRows} rows</p>
                <p><strong>Unique Campaigns in File 2:</strong> ${result.stats.uniqueCampaignsInFile2}</p>
                <p><strong>Suggested URLs Found in File 2:</strong> ${result.stats.foundCount}</p>
                <p><strong>Suggested URLs Not Found in File 2:</strong> ${result.stats.notFoundCount}</p>
                <p><strong>Percentage of Suggested URLs that exist:</strong> ${result.stats.processedRows > 0 ? ((result.stats.foundCount / result.stats.processedRows) * 100).toFixed(2) : 0}%</p>
            `;
            
            // Status counts
            document.getElementById('foundCount').innerHTML = `Found ${result.stats.foundCount} suggested URLs that exist in File 2`;
            document.getElementById('notFoundCount').innerHTML = `Found ${result.stats.notFoundCount} suggested URLs 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 Compare 2 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 Compare 2 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 two Excel files:
	<ul>
	<li>File 1: Contains "Campaign" and "Suggested Final URL" columns</li>
	<li>File 2: Contains "Campaign" and "Final URL" columns</li>
	</ul>
	</li>
	<li>Joins both files by the "Campaign" column</li>
	<li>Compares the joined data to find:
	<ul>
	<li>Campaigns where "Suggested Final URL" values appear in "Final URL" values</li>
	<li>Campaigns where "Suggested Final URL" values don't appear in "Final URL" values</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</h2>
	<p>This file contains campaign names and suggested 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">Suggested Final URL Column:</label>
	<input type="text" id="file1UrlCol" value="Suggested Final URL" placeholder="e.g. Suggested Final URL, Suggested URL">
	</div>
	</div>
	</div>

	<!-- File 2 Section -->
	<div class="file-section">
	<h2>File 2</h2>
	<p>This file contains campaign names and final 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="file2CampaignCol">Campaign Column:</label>
	<input type="text" id="file2CampaignCol" value="Campaign" placeholder="e.g. Campaign, Campaign Name">
	</div>
	<div class="column-input">
	<label for="file2UrlCol">Final URL Column:</label>
	<input type="text" id="file2UrlCol" value="Final URL" placeholder="e.g. Final URL, URL, Link">
	</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>Suggested URLs that were found in File 2</h2>
	<p>These campaigns have "Suggested Final URL" values that appear among "Final URL" values 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</th>
	<th>Suggested Final URL</th>
	<th>Matching 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>Suggested URLs that were NOT found in File 2</h2>
	<p>These campaigns have "Suggested Final URL" values that don't appear among "Final URL" values 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</th>
	<th>Suggested 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_Suggested_URLs');
	});

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

	// Function to process both files
	async function processFiles() {
	const file1Input = document.getElementById('file1');
	const file2Input = document.getElementById('file2');
	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 file2CampaignCol = document.getElementById('file2CampaignCol').value.trim();
	const file2UrlCol = document.getElementById('file2UrlCol').value.trim();

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

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

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

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

	// Process the data
	const result = processData(
	file1Data, file2Data,
	file1CampaignCol, file1UrlCol,
	file2CampaignCol, file2UrlCol
	);

	// 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 both files
	function processData(
	file1Data, file2Data,
	file1CampaignCol, file1UrlCol,
	file2CampaignCol, file2UrlCol
	) {
	// Normalize column names (find the actual case-sensitive names)
	const file1Cols = normalizeColumns(file1Data[0], {
	campaign: file1CampaignCol,
	url: file1UrlCol
	});

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

	// Create a mapping from Campaign to Final URLs in File 2
	const campaignToUrlsMap = {};
	file2Data.forEach(row => {
	if (row[file2Cols.campaign] && row[file2Cols.url]) {
	const campaign = row[file2Cols.campaign];
	const url = row[file2Cols.url];

	if (!campaignToUrlsMap[campaign]) {
	campaignToUrlsMap[campaign] = new Set();
	}
	campaignToUrlsMap[campaign].add(url);
	}
	});

	// Process file1 and check against file2
	const foundInFile2 = [];
	const notFoundInFile2 = [];
	let processedRows = 0;
	let skippedRows = 0;

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

	processedRows++;

	// Check if this campaign exists in file2 and if the suggested URL matches any final URL
	if (campaignToUrlsMap[campaign] && campaignToUrlsMap[campaign].has(suggestedUrl)) {
	foundInFile2.push({
	campaign: campaign,
	suggestedUrl: suggestedUrl,
	matchingUrl: suggestedUrl // They're the same in this case
	});
	} else {
	notFoundInFile2.push({
	campaign: campaign,
	suggestedUrl: suggestedUrl
	});
	}
	} else {
	skippedRows++;
	}
	});

	return {
	foundInFile2,
	notFoundInFile2,
	stats: {
	file1Count: file1Data.length,
	file2Count: file2Data.length,
	processedRows,
	skippedRows,
	foundCount: foundInFile2.length,
	notFoundCount: notFoundInFile2.length,
	uniqueCampaignsInFile2: Object.keys(campaignToUrlsMap).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.campaign)}</td>
	<td>${escapeHTML(item.suggestedUrl)}</td>
	<td>${escapeHTML(item.matchingUrl)}</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.campaign)}</td>
	<td>${escapeHTML(item.suggestedUrl)}</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>Processed Rows from File 1:</strong> ${result.stats.processedRows} rows</p>
	<p><strong>Skipped Rows (missing data):</strong> ${result.stats.skippedRows} rows</p>
	<p><strong>Unique Campaigns in File 2:</strong> ${result.stats.uniqueCampaignsInFile2}</p>
	<p><strong>Suggested URLs Found in File 2:</strong> ${result.stats.foundCount}</p>
	<p><strong>Suggested URLs Not Found in File 2:</strong> ${result.stats.notFoundCount}</p>
	<p><strong>Percentage of Suggested URLs that exist:</strong> ${result.stats.processedRows > 0 ? ((result.stats.foundCount / result.stats.processedRows) * 100).toFixed(2) : 0}%</p>
	`;

	// Status counts
	document.getElementById('foundCount').innerHTML = `Found ${result.stats.foundCount} suggested URLs that exist in File 2`;
	document.getElementById('notFoundCount').innerHTML = `Found ${result.stats.notFoundCount} suggested URLs 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>