# Excel Gurus: Transpose help needed. **URL:** \ **Category:** Factual Questions\ **Created:** [April 28, 2010, 2:02am UTC](https://boards.straightdope.com/t/excel-gurus-transpose-help-needed/537590 "2010-04-28T02:02:37Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![xash](https://avatars.discourse-cdn.com/v4/letter/x/c6cbf5/32.png) [@xash](https://boards.straightdope.com/u/xash)\ **Post date:** [April 28, 2010, 2:02am UTC](https://boards.straightdope.com/t/excel-gurus-transpose-help-needed/537590/1 "2010-04-28T02:02:37Z")
Sheet 1: Name Listing 1 Listing 2 Tom CA AZ Dick MI NY Harry CA OH Sheet 2: Name CA AZ MI NY CA OH Tom Dick Harry How do I get, in Sheet 2, a “1” for every match of Name/Listing and a “0” for every non-match? Problem is, dragging it to copy across cells screws up because of the absolute/relative thingie. More specifics will be provided if needed, but this is the gist of it. Thanks. ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [April 28, 2010, 2:45am UTC](https://boards.straightdope.com/t/excel-gurus-transpose-help-needed/537590/2 "2010-04-28T02:45:46Z")
I think you want to look into using a pivot table. Alas, pivot tables and I don’t get along well, so I’ll leave the details to someone else. ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [April 28, 2010, 3:29am UTC](https://boards.straightdope.com/t/excel-gurus-transpose-help-needed/537590/3 "2010-04-28T03:29:10Z")
I did this on the same sheet, but here’s the concept… “Name” in the first table is in cell A1 “Name” in the second table is in cell A6 The formula in B7 is: =IF(ISNA(HLOOKUP(B$6,$B2:$C2,1,FALSE)),0,1) This can be copied across and down. Make sure you have the dollar signs correct. It assume the names in the two tables are in the same order. ---
**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\ **Post date:** [April 28, 2010, 1:19pm UTC](https://boards.straightdope.com/t/excel-gurus-transpose-help-needed/537590/4 "2010-04-28T13:19:22Z")
If you can’t make that assumption, you can use the MATCH and INDEX functions to do lookups in both directions. How to do this is a bit more complicated than I can fit into a messageboard post, though … ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [April 28, 2010, 2:25pm UTC](https://boards.straightdope.com/t/excel-gurus-transpose-help-needed/537590/5 "2010-04-28T14:25:52Z")
Can be done with a simple enough vlookup if you transform sheet 1 as below; Name Listing Concatenate List Tom CA TomCA 1 Dick MI DickMI 1 Harry CA HarryCA 1 Tom AZ TomAZ 2 Dick NY DickNY 2 Harry OH HarryOH 2 Then in each cell within the grid on sheet 2 your formula (if you have Exel 2007) is: =IFERROR(VLOOKUP(TRIM($A2)&TRIM(B$1),$C$2:$D$7,2,FALSE),0) This will display 1 if the state is in list 1, 2 if in list 2 or 0 if no match. If you want it 1 and zero, then set all the values in column D of sheet 1 to be 1. e.g. CA AZ MI NY OH Tom 1 2 0 0 0 Dick 0 0 1 2 0 Harry 1 0 0 0 2 If you don’t have Excel 2007 you can get the 1-0 result using the =IF(ISNA(VLOOKUP(TRIM($A2)&TRIM(B$1),$F$4:$G$9,2,FALSE)),0,1) formula much as indicated by **k364** , but in this case the names/states can be in any sequence. ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [April 28, 2010, 3:02pm UTC](https://boards.straightdope.com/t/excel-gurus-transpose-help-needed/537590/6 "2010-04-28T15:02:38Z")
Glad I didn’t embarrass myself further. I really should learn not to post after 10 pm. 🙂 ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [April 29, 2010, 12:29am UTC](https://boards.straightdope.com/t/excel-gurus-transpose-help-needed/537590/7 "2010-04-29T00:29:23Z")
KneadToKnow: If the data was arranged as below a pivot table would be, by far, the easiest and most elegant solution. A couple of clicks and I had the desired answer. Pivot tables are the most powerful, but not often used features of Excel. ```auto Name Listing Tom CA Dick MI Harry CA Tom AZ Dick NY Harry OH ``` ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [April 29, 2010, 12:52am UTC](https://boards.straightdope.com/t/excel-gurus-transpose-help-needed/537590/8 "2010-04-29T00:52:37Z")
Assuming that you start on A1 on each tab and that names are same order: =IF(COUNTIF(Sheet1!$B2:$C2,B$1)\>0,1,0) If you want to put names in any order you want on either page, you’ll need to create a dummy variable in the 4th column on sheet 1 (next to Listing 2) and just number the cells 1,2,3,4 (going down - so 1 across from the first name, 2 across the second, etc.) Then you get to use this formula 🙂 =IF(COUNTIF(OFFSET(Sheet1!$B$1:$C$1,VLOOKUP($A2,Sheet1!$A$2:$D$4,4,FALSE),0,1,2),B$1)\>0,1,0) Make sure to set the cell range for the vlookup to be the entire table on sheet 1. That should do it. ---
**Author:** ![xash](https://avatars.discourse-cdn.com/v4/letter/x/c6cbf5/32.png) [@xash](https://boards.straightdope.com/u/xash)\ **Post date:** [April 29, 2010, 10:24am UTC](https://boards.straightdope.com/t/excel-gurus-transpose-help-needed/537590/9 "2010-04-29T10:24:19Z")
Thanks a lot guys. Multiple solutions above worked.