Wednesday, July 23, 2014

yogi_Merge Data From Sheets Named 'ATC' And 'Users' With Different Field Configurations Into A Single Sheet With Common Field Configuration

                            Google Spreadsheet   Post  #1706
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Jul-23-2014
post by:archiewood :
Combining pages into a single sheet
Hi all,

I've got a sheet with three pages, two of which are populated by (separate) forms. Both forms collect information about the same thing, but one collects less than the other. Form B's fields are all also contained in Form A, but not vice versa.

I would like to be able to combine the two sets of results into one sheet.

Table 1:
  A      B    C
1 David  32   M2 Tom    24   M3 Sarah  27   FTable 2:
  A      B
1 Alan   32
2 Suzi   25
3 Claire 31
Table 3:
  A      B    C1 David  32   M2 Tom    24   M3 Sarah  27   F4 Alan   32
5 Suzi   25
6 Claire 31

Table 3 above is the target. Is this possible?
Hi Ivanna, thanks for the effort you're making trying to help me. I clearly haven't effectively communicated the problem to begin with, so I'll try again.

The problem isn't just that the two sheets have different numbers of columns, but that the columns that do match are in a different order. My example did not reflect this. This is the first few columns from the actual sheets:

Sheet ATC:

Timestamp  |Callsign (if applicable)  |Aircraft registration  |Aircraft type              |Type of flight
Sheet Users:
Timestamp  |Captain                   |Aircraft operator      |Callsign (if applicable)   |Aircraft registration

There is some commonality between the two, but this out-of-step order is typical of the whole thing. Clearly if I just lump the rows of ATC and Users together, the result will be a meaningless mess.

Presenting it this way makes me think it was ridiculous to even ask whether a formula could reorganise these with any efficiency. I think the best thing is to use one of your earlier formulas, and just put blocking columns in the 'smaller' sheet so that the columns will line up once they're combined?
I don't see Ivanna around at the moment, so allow me to suggest that you the solution I have proposed in the following: