CODEWITHSUNDEEP
excel
ray
ray

Reputation: 7

how do i combine data based on rows and columns?

I don't even know where to begin. I need sheet 1 to create a data that combines rows and columns. basically 

    on sheet 2 cell A1 should have cat + blue + active
and on sheet 2 cell A2 should have cat + orange + active

Sheet1                 
row  row     column         
cat  blue    active     
dog  orange  slow       
     green                  


Sheet2
       COLUMN1         COLUMN2
ROW 1  catblueactive    catblueslow
ROW 2  catorangeactive  catorangeslow
ROW 3  catgreenactive   catgreenslow
ROW 4  dogblueactive    dogblueslow
ROW 5  dogorangeactive  dogorangeslow
ROW 6  doggreenactive   doggreenslow

I dont know how much information sheet one will have so it may be up to 15 rows long and 10 columns long.

it doesnt need to be on different sheets, i just used for ease of example.

Upvotes: 0

Views: 54

Answers (1)

CynicalSection
CynicalSection

Reputation: 704

It seems you need a Cartesian product between your columns. If you try to avoid VBA, check this link

UPDATE: details about the solution (verified with Excel 2013)

  1. for each column, go to Data->Get&Transform group -> From Table and fill the range. Click OK
  2. Query editor window will appear. Go to Close and Load -> Close and Load to->Only Create Connection
  3. After you finish creating connections, go to first query in the Workbook Queries pane and double click it.
  4. We are now in Query editor window. Go to Add Column tab, Add custom column. In Custom column formula, write the name of the table(e.g. Table2). Add columns for other remaining tables too. When finished, press expand button at the top of the column. Apply for all columns.
  5. Now, you can create additional custom columns and add any formula you want. In Add Custom column window, you have all available columns from Query editor window and you can use them in formulas.

Upvotes: 1

Related Questions