title: Extract data from specific columns with the same name in multiple Excel files and sheets
body: <p>I have 20 folders with varying number of excels (as .xlsx) files in each folder. Each excel file has varying number of sheets and sheets has varying number of columns and different column names on the first row. The second row has column names which has a common name say "Number" (as shown in the screenshot of excel below). 
  I need to extract unique values from specific columns with the name "number" in each sheet, from each excel, from each folder and then sum up to a column with unique values thus gathered.</p>
  
  <p>Since, I am not that expert in excel, I tried with just one excel with the below code.</p>
  
  <pre><code>import os
  import pandas as pd
  
  workbook = r'C:\Users\Material.xlsx'
  df = pd.concat(pd.read_excel(workbook, sheet_name = None , usecols = ['Number'], header = 1))
  df = df.Number.unique()
  df
  
  
  </code></pre>
  
  <p>I had faced this problems here:</p>
  
  <ol>
  <li>this script reads all the sheets from an excel but if there is varying number of column with same name then it reads only the first column. This should not be the case.
  EX: I should get a unique column with all the unique values in column "Number" as in the "Sheet1" in the below screenshot.</li>
  <li>It returns an array and I want in a df.</li>
  </ol>
  
  <p>Also tried this code:</p>
  
  <pre><code>import os
  import pandas as pd
  folder_path = os.chdir(r'C:\Users\Material.xlsx')
  files = os.listdir(folder_path)
  print(files)
  df2 = pd.DataFrame()
  for i in range (len(files)):
      df = pd.read_excel(files[i], header=1)
      df1 = df.filter(regex='Number')
      df2 = pd.concat([df2, df1], axis=1, sort=False)
      i = i+1
  df2 = df2.filter(regex='Number')
  df2
  df2.to_excel(r"r'C:\Users\output.xlsx', index = False)
  
  </code></pre>
  
  <p>The issues here are:</p>
  
  <ol>
  <li>I get only the first column values in case there are many columns with same name.</li>
  <li>Only single sheet is taken, other sheets in an excel are not considered.</li>
  </ol>
  
  <p>Please help</p>
  
  <p><a href="https://i.stack.imgur.com/9a3zc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9a3zc.png" alt="enter image description here"></a></p>